Home > Formulas > Date > FAQs

# Excel Date and Time FAQ

Answers to frequently asked questions about Excel date and time calculations. Also see date formula examples on the Date Function Examples page on this site.

## Excel Time Calculations

### How do I add times together?

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

### How do I subtract time?

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.

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

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

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

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

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

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

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

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, why do they end up one day (or 4 years) wrong?

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.

### How do I add 3 months to an Excel date?

Type the date in cell A1, then use one of the following formulas in cell A2, or in any other empty cell:

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

OR, use the EDATE function:

• =EDATE(A1, 3)

### Which formula calculates last day of a month?

Type the date in cell B2, then use one of the following formulas in cell B4, or in any other empty cell:

1) Use the DATE function, with YEAR and MONTH. The last day of the month equals the zero'th of next month for some strange reason, so use this formula

• =DATE(YEAR(B2),MONTH(B2)+1,0)

2) OR, use the EOMONTH function, with zero as the number of months:

• =EOMONTH(B2,0)

### Why does Excel think 1900 is a leap year? It's not.

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

## Excel Date and Time Resources

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

Also see the Date Function Examples page on this site.

Excel Dates Fix Format

Nth Weekday in Month