1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

excel formula to subtract text format date\time

Discussion in 'Ask an Excel Question' started by IKHAN, Jan 11, 2017.

  1. IKHAN

    IKHAN Member

    Messages:
    209
    Hi,

    Require excel formula to subtract date\time(In text format column B and C) and give results in Column D (output column)

    Using =($C4-$B4)*1440 in output column gives #VALUE
    date format : ddd mmm dd, yyyy - hh:mm AM/PM


    Date 1 Date 2 Output
    Sun Jan 11, 2017 - 09:00 AM Sun Jan 11, 2017 - 09:30 AM #VALUE!
    Sun Jan 11, 2017 - 11:30 PM Sun Jan 12, 2017 - 09:40 AM #VALUE!
    Sun Jan 11, 2017 - 09:40 AM Sun Jan 11, 2017 - 09:50 AM #VALUE!
    Sun Jan 11, 2017 - 09:50 AM Sun Jan 11, 2017 - 10:30 AM #VALUE!
    Sun Jan 11, 2017 - 11:00 AM Sun Jan 11, 2017 - 11:59 PM #VALUE!

    Thank you and any assistance will be appreciated.

    Attached Files:

  2. Speedy Sam

    Speedy Sam New Member

    Messages:
    1
    Hi Ikhan

    Your formula is correct. The problem is that the dates you are seeing are just text and not dates that Excel can perform a calculation with. When you try to add or subtract text you get the #VALUE error.

    To fix this, you will need to re-enter your dates. Excel will convert your dates to numbers and allow you to do calculations.

    If you only have a few dates per your example, then it is easiest to re-key in the format:
    • 11/01/2017 09:00:00
    Then format the cells to appear in your preferred format.
    • ddd mmm dd, yyyy - hh:mm:ss AM/PM
    Hope that helps.
  3. IKHAN

    IKHAN Member

    Messages:
    209
    Few dates are just example..have more than 300 line of dates and this dates are pulled thru a macro. Formula should help to subtract from text format dates.
  4. Chihiro

    Chihiro Well-Known Member

    Messages:
    2,884
    If you can't alter date string for some reason... you can do following.
    =((MID(C4,FIND(" ",C4)+1,FIND("~", SUBSTITUTE(C4," ","~",4))-FIND(" ",C4)-1)+0+RIGHT(C4,8))-(MID(B4,FIND(" ",B4)+1,FIND("~", SUBSTITUTE(B4," ","~",4))-FIND(" ",B4)-1)+0+RIGHT(B4,8)))*1440
    IKHAN likes this.
  5. IKHAN

    IKHAN Member

    Messages:
    209
    Thanks Chihiro..It worked

Share This Page