TODAY & WEEKDAY Formula in Google Sheets
When working with spreadsheets, you often have to deal with dates and calculate day differences between a start and end date.
What is the TODAY Formula?
The TODAY function is a basic date function that returns the current day.
It’s very simple to use because unlike other functions, it doesn’t have any arguments. The syntax of the function is:
=TODAY()
Just click on an empty cell and type this function to insert the current date.
The TODAY function typically displays the dates in DD/MM/YY or MM/DD/YY format, depending on your location and locale settings.
You can change this format in the Format > Number > More formats > More date and time formats option in the menu.
The function is a volatile function, which means that it updates itself every time as your sheet containing the function changes. The cells with a TODAY formula will update each day.
Apart from inserting the current time in your sheet, the TODAY function can also be used in other calculations that need this information.
For example, it’s often used to calculate how many days have passed since or how many days are left until specific dates.
In the background, every date is stored as a simple integer number in Google Sheets. Thus, you can calculate date differences by simply subtracting the dates.
To calculate how many days are left until New Year’s Eve, you can use the following formula:
=DATE(2021,1,1) - TODAY()
So you simply need to define the two dates with the DATE function and the TODAY function and subtracting the earlier one from the latter one.
What is the WEEKDAY Formula?
Many times, you need to know the day of the week for specific dates you work with.
The WEEKDAY function can help you with this. It takes a date and returns a number between 1-7 representing the day of the week of the date provided.
The syntax of the WEEKDAY function is:
=WEEKDAY(date, [type])
The date argument is the date for which you want to return the day of the week.
The optional type argument specifies which numbering system to use to represent the weekdays. By default, it counts starting with Sunday = 1.
- If the type is 1, the days are counted from Sunday (1) and the value of Saturday is 7.
- If the type is 2, the days are counted from Monday (1) and the value of Sunday is 7.
- If the type is 3, days are counted from Monday and the value of Monday is 0 and the value of Sunday is 6.
You must write the date in a valid format, that can be either a date defined by a DATE or TODAY function or a cell reference to a cell containing a date.
For example, returning the weekday of today and New Year’s Eve would look like this:
=WEEKDAY(TODAY()) (in Cell A1 in example below)
=WEEKDAY(DATE(2021,1,1)) (in Cell B1 in example below)
Here the default numbering system was used, so the today is a Wednesday and the day of New Year’s Eve 2021 is Friday.
This information can be used in your calculations where you want to compute something based on the day of the week…
More TODAY & WEEKDAY Formula Examples
For example, you can determine whether your specific dates are Fridays with the following IF formula:
=IF(WEEKDAY(A2)=6,"Yes","No")
Summary
It’s a simple function, but if you are doing some semi-automated reporting, it can save you a lot of headaches.