summing of two dates

f_mayr

New Member
Joined
Jul 7, 2009
Messages
16
hey

i have a question regarding the summing of two dates

i have

A1: 20 Days 19:46:49 Hours
A2: 12 Days 01:30:19 Hours
B1: =A1+A2 = 01 Days 21:17:08 Hours

All 3 cells with this formating: TT "Days" hh:mm:ss "Hours"

A1 and A2 are results of calculating formulas.

now the first problem is obvious. the total (B1) should be 1 month 01 Days 21:17:08 Hours.
this is achieved by changing the formating to: MM "Months" TT "Days" hh:mm:ss "Hours

now the problem i have is that A1 and A2 both count 1 month if i format them to MM "Months" TT "Days" hh:mm:ss "Hours
That means with this formating i have:


A1: 01 Months 20 Days 19:46:49 Hours
A2: 01 Months 12 Days 01:30:19 Hours
B1: =A1+A2 = 02 Months 01 Days 21:17:08 Hours

that is not correct! why is excel giving them one month even if there is no month in the calculation result..?

The correct result would be:
A1: 00 Months 20 Days 19:46:49 Hours
A2: 00 Months 12 Days 01:30:19 Hours
B1: =A1+A2 = 01 Months 01 Days 21:17:08 Hours

hope you can help me..

thanks
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
You can't use month and day in a number format like that. MM returns the month number of a date and TT (DD in the UK) returns the day number. Adding 20 Jan 1900 to 12 Jan 1900 returns 1 Feb 1900, which is MM 02 and DD 01, as the result of your formula shows.
 
Upvote 0
ok

for the records:

i think it should work if i format the whole thing as:
[hh]:mm:ss "Hours"

with the fact that i only have hours instead of days and hours. but i dont care about that in my case..
 
Upvote 0
another question reagarding that:

i tried to run a countif over that and its not working...

means i have following

A1: 07:26:21 Hours
A2: 01:41:49 Hours
A3: 329:54:09 Hours

all 3 are formatted like this: [hh]:mm:ss "Hours"

and i need fex to count all those who are less than 300 hours. in this case the first two..

how is that possible?

thx
 
Upvote 0
ps i have a workaround using the values with general formating.

as instead of 300 hours (in [hh]:mm:ss "Hours") use 12.5 (in general) as criteria.. dont know if that is consitent at all..
 
Upvote 0

Forum statistics

Threads
1,215,554
Messages
6,125,487
Members
449,233
Latest member
Deardevil

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
Back
Top