calculate difference bt date & time

almouchie

Board Regular
Joined
Dec 23, 2004
Messages
128
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

If you have date and time in same cell:

=B1-A1

and format as [h]:mm
 

almouchie

Board Regular
Joined
Dec 23, 2004
Messages
128
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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

Well-known Member
Joined
Jan 9, 2003
Messages
632

ADVERTISEMENT

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

Board Regular
Joined
Dec 23, 2004
Messages
128
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638

ADVERTISEMENT

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

Board Regular
Joined
Dec 23, 2004
Messages
128
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Switch place between month and day in your dates.

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

Sade

Board Regular
Joined
Nov 29, 2004
Messages
145
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.
 

Forum statistics

Threads
1,147,694
Messages
5,742,671
Members
423,746
Latest member
Joaogomes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top