Date conversion

**ssr123** · 10-09-2012, 11:37 AM

I have date time in the following format in Excel

say

A1 = Aug 14 2012 11:44AM
B1 = Aug 16 2012 3:19PM

I need to do subtract the time and report it in minutes. How can I do it in Excel.

I get #Value when I try =(B1-A1)*1440
Thanks!

**6StringJazzer** · 10-09-2012, 02:27 PM

Welcome to the Forum!

subtract two datetimes to get minutes.xlsx

This works for me. I am guessing that you have text in those cells rather than actual date/time values. That will cause a #VALUE error. See my attachment for the correct way to do this.

**Pepe Le Mokko** · 10-09-2012, 02:43 PM

Originally Posted by 6StringJazzer

I am guessing that you have text in those cells rather than actual date/time values. That will cause a #VALUE error. See my attachment for the correct way to do this.

I don't think the problem lies there. As the two values are used in an arithmetic operation, the text is immediately coerced to values. It would be interesting to see the real sheet

**ssr123** · 10-09-2012, 03:58 PM

Hi

I have attached the sample xlsx.

Thanks

**Haseeb Avarakkan** · 10-09-2012, 04:11 PM

Try,

=(SUBSTITUTE(TRIM(REPLACE(B1,LEN(B1)-1,0," "))," ",", ",2)-SUBSTITUTE(TRIM(REPLACE(A1,LEN(A1)-1,0," "))," ",", ",2))*1440

**ssr123** · 10-09-2012, 04:17 PM

Thank you so much that worked!

**6StringJazzer** · 10-10-2012, 06:55 AM

The data is presented in text form, and it's a date/time format that is not recognized and therefore not automatically coerced. The formula above solves your immediate problem (and maybe that's enough) but in general, date/time data should be entered as date/time data rather than text. The formula converts the existing data into date/time data by removing the leading space, adding a comma after the day number, and inserting a space before AM/PM. This method could be used to modify all your data. You would then have to reformat from General to a Custom format of "Mmm d, yyyy hh:mm AM/PM".