Contextures

Excel Date and Time FAQ

Answers to frequently asked questions about Excel date and time calculations

Excel Time Calculations

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

How do I add times together? go to top

Just add together like any number (=A1+A2+A3). Use custom number format [h]:mm in the result cell to prevent rollover at 24 hours (see the screen shot in question "When I try to sum the time data..." below.)

How do I subtract time? go to top

Just subtract, like =B1-A1. 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.

How can I sum times in the format: 5:20, 12:02, and get a total like this-- 50:07. go to top

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

custom number format

How do I add cells with seconds in them, i.e... 25, 50... and get this format -- 1:10 go to top

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

How do I calculate 0:45 minutes at $120 per hour?

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

How do I calculate hours worked for a shift that ends after midnight? go to top

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)

Excel Date Calculations

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

How can I enter the date into a cell so it doesn't change every day? go to top

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

When I copy and paste Excel dates, they end up one day (or 4 years) wrong. go to top

One workbook is using 1904 calendar, the other one 1900 (in the first example, years are not displayed, but definitely present). Go menu Tools>Options, Calculation and make them equal, preferably also correct if you know what the dates was supposed to be.

How do I add 3 months to an Excel date?

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

Which worksheet functions would emulate EOMONTH; last day of month? go to top

The last day of the month equals the zero'th of next month for some strange reason: =DATE(YEAR(A1),MONTH(A1)+1,0)

Excel thinks 1900 is a leap year. It's not. go to top

Yes it does and it's not. The following MSKB article explains the reason:
     Excel incorrectly assumes that the year 1900 is a leap year
        http://support.microsoft.com/kb/214326

Excel Date and Time Resources

  1. Where can I find more information on Excel dates and times?

Where can I find more info on Excel dates and times?

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.

More FAQs

FAQ Menu

Excel Files

Functions and Formats

Macros

Pivot Tables

Excel Resources

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

Top of Page | Main Index

Search Contextures Sites

 

 

 

Free Pivot Table Tools

 

 

Excel UserForms for Data Entry

 

Last updated: December 21, 2016 3:31 PM
Contextures RSS Feed