Excel help

kwyjibo · 21 Jan 2012 at 11:20

Hi, does anyone have any idea how I can find the time duration between two dates/times in excel 2007 in minutes? Unfortunately the cell format is in the form of "YYYY.MM.DD HH:MM" so, for example:

-in A1 2012.01.01 00:00
-in A2 2012.01.01 00:30
-and I would like A3 to calculate automatically 30 minutes

I've tried just setting a custom cell format but that hasn't worked and I don't want to do a delimited text to column sort because it would mess up my other data

thanks

Grenache · 21 Jan 2012 at 12:18

If the underlying data is a date/time number, the integer part is the days and the decimal the day fractions. So, subtract one from the other and multiply by 24 * 60, and the result will be in minutes which you can display in a number format.

Thus:

A3 = (A2-A1) * 24 * 60

Grenache · 21 Jan 2012 at 12:20

Sorry, should have said that if A1 and A2 are text fields, use a text conversion function to get its numeric equivalent

kwyjibo · 23 Jan 2012 at 09:30

Yeah that is the big problem, my dates/times are text (copied and pasted from elsewhere), but =TEXT(A2,"yyyy.mm.dd hh:MM")-TEXT(A1,"yyyy.mm.dd hh:MM") doesn't seem to convert it, any ideas?

Malakite · 23 Jan 2012 at 13:01

Can you just use a find and replace on your date time column to replace the . with a /

This will let you just do the A2-A1 to see the time difference. =A2-A1 will show you the difference if you format the cells as hh:mm however this wont work if you have any times that exceed 24 hours. If this is the case just use =(A2-A1)*24 and format as a humber to see the difference in a h.mm decimal format.

or just the the forumla posted by grenache to see straight minutes.