Dates and DATE practice exercises
A date in a spreadsheet is a number of days, so you can subtract dates, add days to them and build them from a year, a month and a day.
How dates work in Excel
Excel stores a date as a number: the count of days since January 1, 1900. That's why you can subtract dates, add days to them and build a date from separate parts. The date you see is only a number with a date format.
=B2-A2 gives the days between an order date in A2 and a delivery date in B2, so Mar 3, 2024 and Mar 10, 2024 give 7. =A2+B2 adds the payment terms in B2 to an invoice date to get a due date. When a system gives you the year, month and day in three columns, =DATE(A2,B2,C2) turns them into one date. =MONTH(A2) returns the month number, and =ROUNDUP(MONTH(A2)/3,0) turns it into a quarter from 1 to 4.
The usual pitfall is the format: a subtraction can show up as a date like Jan 7, 1900. Change the cell format to General or Number and you see 7. The exercises below cover days between dates, a due date, DATE, MONTH and the quarter.
- 1Days between two datesIn C2, work out how many days the delivery took. Subtract the order date in A2 from the delivery date in B2.
- 2Add days to get a due datePremiumIn C2, work out the due date of the invoice. Add the payment terms in B2 to the invoice date in A2.
- 3Build a date with DATEPremiumThe year, month and day of a date came from a system in three columns. In D2, use DATE to turn A2, B2 and C2 into one date.
- 4Get the month with MONTHPremiumTo total sales by month you need the month number of each sale. In B2, use MONTH to get it from the date in A2.
- 5Get the quarter from a datePremiumIn B2, find the quarter of the sale date in A2, a number from 1 to 4. Divide the month by 3 and round up: ROUNDUP(MONTH(A2)/3,0).
Functions in this topic
Read the guide
Questions
Why does my date show as a number in Excel?
The cell has a number format, not a date format. Excel keeps the date as a day count, such as 45366 for Mar 15, 2024. Select the cell and choose Short Date or Long Date in the number format box on the Home tab. The value stays the same.
How do I calculate the number of days from a date until today?
Subtract with TODAY: =TODAY()-A2 gives the days since the date in A2, and =A2-TODAY() gives the days left until a date in the future. TODAY updates every time the workbook recalculates, so the answer changes from day to day. Format the result cell as General or Number.
Why can't Excel calculate with my dates?
The dates are probably text. Text sits at the left edge of the cell, while real dates line up at the right. Try =DATEVALUE(A2) to turn a text date into a real one, or retype the date in a format your Excel recognizes. Then subtracting and adding days works.