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