# calculate difference bt date & time

#### almouchie

##### Board Regular
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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### fairwinds

##### MrExcel MVP
Hi,

If you have date and time in same cell:

=B1-A1

and format as [h]:mm

#### almouchie

##### Board Regular
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
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
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

See if that solves it for you.

#### almouchie

##### Board Regular
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
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
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
Switch place between month and day in your dates.

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

##### Board Regular
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.

Replies
1
Views
417
Replies
3
Views
250
Replies
3
Views
591
Replies
6
Views
178
Replies
2
Views
175

1,181,994
Messages
5,933,158
Members
436,882
Latest member
Kiddo456

### 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.

### Which adblocker are you using?

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

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