calculate difference bt date & time

[almouchie]

how can i calculate the difference between
a date & time with another date & time to give the number of hours & minutes
ex. duration between 20/01/05 21.40 &
21/01/05 22.35
= 55 minutes
diiference in number of hours & minutes or minutes
thanks

 

[fairwinds]

Hi,

If you have date and time in same cell:

=B1-A1

and format as [h]:mm

 

[almouchie]

i have 20/01/05 21:40 & 21/01/05 22:35 in 2 cells
& changed the format . i already tried that subtraction & changed format but it gave me #value!
why?

 

[fairwinds]

Then I guess you do not have true date time values.

Even if they were text, the formula should work if they are according to a format that your regional settings recognice.

So there might be either worng format or some extra spaces in the cell.

Try converting them to true dates by using "text to columns" (there is an option in the last step)

Or try:

=TRIM(B1)-TRIM(A1)

 

[ken2step]

I think if you enter your date time in this format

month/day/year space hour:minute

and do what Fairwinds told you it works fine.

When you entered

day/month/year space hour:minute

excel read it as text.

See if that solves it for you.

 

[almouchie]

IT DIDNT WORK STILL GIVING ME #VALUE
WHY I AM WRITE (25/01/05 21:40) FORMATED AS [h]:mm
even tried 25.01.05 & text to column option
the times & dates r correct (there r 24hr in any day so what the problem

 

[fairwinds]

You must make sure that you have a true date/time value and not text.

You can ue the =ISNUMBER(A1) formula to test. This formula must return TRUE else you cannot use your values for calculations.

 

[almouchie]

it worked fine when the dates are the same but i had
7/01/05 19:35 & 08/01/05 12:54
it returened 737:19 ? it should be 17:19
how to fix that

 

[fairwinds]

Switch place between month and day in your dates.

E.g. 7/01/05 19:35 --> 1/07/05 19:35

 

[Sade]

A follow up question on this same issue. I'm trying to do the same thing (retuning total minutes), but my dates and times are in different cells.

ie: cells A1, B1 are date1 and time1 respectively. While cells C1, D1 are date2 and time2 respectively.

They are in proper date and time format. The "isnumber" test returns true. I tried just joining the different cells and subtracting by the following: =(A1 & B1) - (C1 & D1). However, after joining the date and time, the "isnumber" test returns false.

I'm trying to keep from having to combine them in other cells, and then referencing those. Also, I tried doing it in pieces (Looking at days, then hours, then minutes), but that got very complicated determinning if it was a full day or not.

thanx for any input.