excel formula to subtract text format date\time

[IKHAN]

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.

 

[Speedy Sam]

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.

 

[IKHAN]

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.

 

[Chihiro]

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]

Thanks Chihiro..It worked