Excel/Adding hours to start time, and changing days
QUESTION: Hi Tom
I am trying to create a spreadsheet that will add hours to a start time, giving me a new time, but I am having trouble when the time goes past midnight, and into the next day. I would like it to give me the time on the next day, and tell me the new day. For example:
Start time is 0800 on Saturday 25th June
First interval is .97 of an hour, I would like to add this to the 0800, to get a new time, and tell me we are still in Saturday.
Second interval is 15.81 hours,I would like to add this to the new time, and tell me we are now into Sunday, etc etc.
Can you help please?
If you put 8:00 AM in a cell, it will actually be stored as .33333333333 or 1/3rd of a day but the date will be day 0 of Jan 1900 since that is the default windows base date. Time and dates are stored together as a single number. The integer part of the number is the number of days measured from the mentioned based date. The fractional/decimal portion of the number represents the time measured in days. So 2.5 would be Jan 2, 1900 at noon.
If you want to work beyond 24 hours and know what day of the week you are working in then it only seems logical to use the actual date. Say I want use your start time.
I would enter in say A1
A1: June 25, 2016 08:00 AM
You can format that cell to appear as you want including showing the day of the week.
then in A2 you could have
=A1+(.97/24) this converts you .97 hours to be measured in Days.
in A3 you could have
format A2 and A3 to show both the time and the day of the week as a minimum.
day of the week is shown as dddd
You could use a custom number format such as dddd, dd/mm/yyyy hh:mm
When I do that in A1:A3 I get:
Saturday, 25/06/2016 08:00
Saturday, 25/06/2016 08:58
Sunday, 26/06/2016 00:46
Hope that gives you enough information to craft a solution.
---------- FOLLOW-UP ----------
QUESTION: Hi Tom, that has worked great, thank you very much, the only thing now is that I have to calculate the time between two of the cells - see below:
Nose of Interface - Actual Time Tail of Interface - Actual time Length of
Saturday, 25/06/2016 10:00 Saturday, 25/06/2016 10:25 00:25
Sunday, 26/06/2016 10:00 Monday, 27/06/2016 10:00 00:00
A simple subtraction seems to be working when the times are on the same day (saturday), but when I have the nose and the tail arriving on different days (Nose arrives Sunday, Tail arrives Monday)it dosent seem to work - it should be 24 hours, rather than 0. Would you know how to solve this please?
in the cell where your formula is to do the subtraction, use a custom format of
the square brackets tells Excel to show total hours rather than converting hours exceeding 24 into whole days.