You are here:

# Excel/Adding hours to start time, and changing days

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

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

=A2+(15.81/24)

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.

--
Regards,
Tom Ogilvy

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

Thanks

Gail

Gail,

in the cell where your formula is to do the subtraction, use a custom format of

[hh]:mm

the square brackets tells Excel to show total hours rather than converting hours exceeding 24 into whole days.

--
Regards,
Tom Ogilvy
Questioner's Rating
 Rating(1-10) Knowledgeability = 10 Clarity of Response = 10 Politeness = 10 Comment Fantastic response, saved my life once again, thanks so much Tom x

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

#### Tom Ogilvy

##### Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

##### Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.