Answers to frequently asked questions about Excel date and time calculations

- How do I add times together?
- How do I subtract time?
- How do I sum the time data in the format: 5:20, 12:02, 20:12 etc. to get 50:07?
- I'm adding cells with seconds, i.e. 25, 50, etc... the result I'd like is 1:10
- I need to calculate 0:45 minutes at 120 per hour - with an answer of 90.
- How do I calculate the hours worked for a shift that ends after midnight?

For 2 cells with times, just add together like any number (=A1+A2+A3).

To add 8 hours to the time in a cell, use this formula: =B2 + 8/24

**NOTE**: Use custom number format
[h]:mm in the result cell to prevent rollover at 24 hours (see the screen
shot in question "How do I sum the time data..." below.)

For 2 cells with times, just subtract, like =B1-A1.

To subtract 3 hours from the time in a cell, use this formula: =B2 - 3/24

**NOTE**: Use Calendar format 1904 if you need to
display negative results; Tools/Options/Calculation and check the 1904
Date System checkbox. Note that 1904 calendar will offset all dates
by 4 years 1 day, so be careful.

Use the custom number format [h]:mm to prevent rollover at 24 hours

Since Excel stores times as fractions of days, in order to convert integers into times you need to divide the sum by (24*60*60) or 86,400

1 is a day; 24 hours. So one hour is 1/24. =A1*B1*24 will bring the desired result. Format result cell as currency or number (it tends to pick the date format by default).

If the start time is greater than the end time, we'll assume that
the shift ended the next day. In that case, we'll add 1 to the end time,
which is the equivalent of adding a full day to the end time. That makes
the end time greater than the start time, and the calculation will work
correctly.

For example, with start time in cell B2 and end time in cell C2, use
this formula:

**=IF(C2="",0,IF(B2>C2,1,0)+C2-B2)**

- How can I enter the date into a cell so it doesn't change every day?
- When I copy and paste Excel dates, they end up one day/four years wrong.
- How do I add 3 months to an Excel date?
- What worksheet functions emulate EOMONTH; last day of month?
- Excel thinks 1900 is a leap year. It's not

To enter the current date, press **Ctrl** + **;**
(hold the Ctrl key while typing a semicolon.)

To enter the current time, press **Ctrl** + **:** (hold
the Ctrl key while typing a colon.)

One workbook is using 1904 calendar, the other one 1900 (in the first example, years are not displayed, but definitely present). Go File>Options, Advanced. In the When calculating this workbook section, select the same setting in both workbooks, for the Use 1904 date system option.

=DATE(YEAR(A1),MONTH(A1)+3,DAY(A1))

The last day of the month equals the zero'th of next month for some strange reason, so this formula is like the EOMONTH function: =DATE(YEAR(A1),MONTH(A1)+1,0)

Yes it does and it's not. The following Microsoft article explains the reason:

**Excel incorrectly assumes that the
year 1900 is a leap year**

https://support.microsoft.com/kb/214326

Chip Pearson's web page on dates and times will give you an understanding of how this works in Excel , and it has lots of useful date and time samples.

**Credits**: Original FAQs compiled by Harald Staff, Excel MVP 2000-2005.
Additions by Debra Dalgleish.

Last updated: June 1, 2021 12:37 PM

Contextures RSS Feed