Date Formula Examples – Excel & Google Sheets
This tutorial will provide an overview of date formulas in Excel & Google Sheets.
In spreadsheets, dates are stored as serial numbers where each whole number represents a unique day. When you enter a date into a cell, the date is automatically converted to a serial number and the cell number formatting is changed to a Date format so the date is readable.
In Excel, the serial number 1 represents the date 1/1/1900.
You can always change the date formatting so the serial number is visible by changing the Number Format to General:
<<example or gif>>
The TODAY Function will output today’s date.
Keep in mind that this date will refresh every time the workbook is recalculated. To hard-code the current date use the shortcut CTRL + ;.
To enter dates in formulas, you can use the DATE Function.
Automatically Fill Dates
If you enter a date, and drag the date down, you can quickly populate a series of dates (although instead, you might consider using a +1 formula instead):
Add or Subtract Days or Weeks to Dates
Because dates are stored as serial numbers, you can simply add (or subtract) whole numbers to dates to add (or subtract) days to a date.
You can also subtract one date from another date to calculate the number of days between them.
To add weeks to a date, simply add the number of weeks multiplied by 7.
Add or Subtract Months or Years to Dates
The EDATE Function is used to add (or subtract) months or years to a date. This example will add months to a date:
To add years to a date, simply add the number of years and multiply by 12.
You can also add a number of business days (workdays or weekdays) to a date with the WORKDAY Function:
Count Workdays Between Dates
Or calculate the number of workdays between two days with the NETWORKDAYS Function:
Each of these functions can also optionally include holidays:
<<example of just one of the functions>>
Extract Information from Date
To extract the Day, Month, or Year numbers from dates use the DAY, MONTH, and YEAR Functions.
The DAY Function calculates the day number of a date.
The MONTH Function calculates the month number of a date (which can be used to calculate the month name as we’ll demonstrate below).
The YEAR Function calculates the year number of a date.
By using the DATE Function along with the DAY, MONTH, and YEAR Functions, you can create a date similar to an existing date. This example will calculate the first day of the year from a date:
<<example of first day of year calc>>
Quarter From a Date
If you need to calculate a date’s quarter you can use the **** :
Get Month Name From Date
To calculate the month name from a date, you can simply change the Number Formatting of a date to see the month name.
Or you can use the TEXT Function to output the month name as text:
To calculate the week number of a date (1-54), use the WEEKNUM Function.
Day of Week Number / Name
The ***** Function will return the day number of a date within a week.
This can be adjusted to start the week on a different day (Ex. Monday instead of Sunday).
Then you can use the CHOOSE Function to calculate the name of the day:
Early we discussed the EDATE Function that allows you to “jump” forwards or backwards a defined number of months. The EOMONTH Function works in the same way except the EOMONTH Function will return the last day of a month:
By adding 1 day to the result of the EOMONTH Function you can calculate the first day of a month:
You can compare dates by using Excel’s standard comparison operators.
<<image / chart>>
<<example / look at formula page>>
Dates can be formatted in many different ways. You can refer to day, months, and years using any of the below options:
And combine them in a variety of ways
You can access these formatting options through the Format Cells menu.
***talk about how to access this…***
Convert Date to Text
We briefly mentioned the TEXT Function above. The TEXT Function allows you to output a number (in this case a date) as text with a specific number format.
This example will output …
Convert Text to Date
If you have a date stored as text, the DATEVALUE Function will convert it to a date:
Date Conditional Formatting
create date range from two dates
extract quarter from date
split date and times into separate cells? probably not
number of days in month
first day of month
day number of year
convert month name to number
years of service
sort by month?
average time, total time