adding date and time in one cell

[droscoe]

don't think this is a real difficult question but couldn't find the answer in previous posts....

I have the date and time entered in one column (dd/mm/yyyy h:mm) and would like to add 5 years, 7 months, 19 days and 12 hours to this value for a second column. My problem is that when the added 12 hours goes in to the next day (happens for any time > 12:00) it does not add an extra day to the date.

For example, when I add 5y, 7mo,19d, 12hr to 25/01/2002 22:46, it gives 13/09/2007 10:36 when I want it to give 14/09/2007 10:36.

When I add 5y, 7mo,19d, 12hr to 08/02/2002 00:23, it gives the correct date-time of 27/09/2007 12:23 (no rollover in this case).

The formula I'm using:
=date(year(a1)+5,month(a1)+7,day(a1)+19)+time(hour(a1)+12,minute(a1),second(a1)

I thought if the date-time is stored as a serial number (ddddd.tttttt) there would not be a rollover problem but it is not working for me. Any thoughts? Thanks

Dave

 

[barry houdini]

Hello Dave, welcome to Mr Excel

The problem you have is that TIME function never gives a value greater than 1 (or 24 hours) so you're not getting 12 hours added as you want, as the time portion of A1 can be obtained by MOD(A1,1) and 12 hours = 0.5 try

=date(year(a1)+5,month(a1)+7,day(a1)+19)+MOD(A1,1)+0.5

 

[Oaktree]

Welcome to the board.

Try just tacking

+(hour(a1)>12)

to the end of your formula

 

[droscoe]

I didn't get the mod function to work. But adding +(hour(a1)>12) to the formula did the trick. Thanks for your replies.

 

[barry houdini]

I didn't get the mod function to work

I don't know why that would be.....see example

Book1
	A	B	C	D
1	25/01/2002 22:46		14/09/2007 10:46
2
3
4
Sheet2

 

[droscoe]

cool. that does work too and is a bit shorter to type. not sure what i mucked up before... cheers