Google spreadsheet is quite useful tool to view, update excel files. I use it quite often. It also has extensive set of formula's which reduces your manual work. Here are some useful Google Spreadsheet Excel DATE Formulas functions.
List only working days dates/weekdays in Google Excel
Sometime we just want to display dates of the weekdays ie. display only monday to friday dates in excel. Here we have a very useful function "Workday" which allows us to display dates which are weekdays. Workday formula calculates the end date after a specified number of working days.
Following is the formula and parameters it takes
WORKDAY(start_date, num_days, [holidays])
To display next weekday date from today's date/any date, we can use following formula
=WORKDAY("29 June 2021", 1)
In above formula, first parameter is the start date and second parameter is the number of days. As we have specified second parameter as 1, above formula will display date as 30 June. If we specify second parameter as 2, above formula will display date as 1 July.
You can also specify third parameter, which is a range or array constant containing the dates to consider holidays. eg. You can list all holidays and pass them as parameter.
If you like to know more about this formula then visit the Google Spreadsheet Documentation about WORKDAY formula.
List only working days dates/weekdays with custom weekends in Online Spreadsheet
Now you might stumbled upon a question - Hey, we don't have holidays on other days and we work on weekends, what about my scenario?
Don't worry. Here is another formula which allows you to specify your holidays as per your choice. This is one of the useful Google Spreadsheet Excel DATE Formulas.
Following is the formula and parameters it takes
WORKDAY.INTL(start_date, num_days, [weekend], [holidays])
Similar to previous formula, here start_date, num_days and holidays parameters are same. But here there is new parameter "weekend", which you can customize as per your requirement.
weekend - [ OPTIONAL - 1 by default ] - A number or string representing which days of the week are considered weekends.
String method: weekends can be specified using seven 0's and 1's, where the first number in the set represents Monday and the last number is for Sunday. A zero means that the day is a work day, a 1 means that the day is a weekend. For example, "0000011" would mean Saturday and Sunday are weekends.
Number method: instead of using the string method above, a single number can be used. 1 = Saturday/Sunday are weekends, 2 = Sunday/Monday, and this pattern repeats until 7 = Friday/Saturday. 11 = Sunday is the only weekend, 12 = Monday is the only weekend, and this pattern repeats until 17 = Saturday is the only weekend.
To display next working day use following formula
=WORKDAY.INTL("4 July 2021", 1, "1000000")
In above formula, I have specified that Monday is weekend. So now above formula will return result as 6 July 2021 as next working day, instead of 5 July 2021.
WORKDAY.INTL works similarly to WORKDAY but also allows weekend days to be specified (for areas where Saturday and Sunday are not considered the weekend).
If you would like to know more about this formula, then visit Google Spreadsheet Documentation about WORKDAY.INTL formula.
Do you know that you can create a Recurring Scheduled Task or CRON Job with Google Apps Spreadsheet Script?
Find number of days between two dates in Excel Spreadsheet
Sometimes we just want to find the number of days between 2 dates. For finding difference between 2 dates we can use DAYS formula. The DAYS function returns the number of days between two dates.
Following is the formula of the DAYS function
DAYS(end_date,start_date)
Following is sample formula find the difference between 2 days
=DAYS("4 July 2021","2 July 2021")
It will return result as 2.
If you would like to know more about DAYS formula, then visit Google Spreadsheet Documentation about DAYS formula.
Find number of working days between two dates in Google Spreadsheet
If you want to find number of working days between 2 dates, then we can use NETWORKDAYS formula. It returns the number of net working days between two provided days.
Following is the formula for NETWORKDAYS function
NETWORKDAYS(start_date, end_date, [holidays])
Following is an example for finding working days between 1 July 2021 and 5 July 2021.
=NETWORKDAYS("1 July 2021","5 July 2021")
It will return result as 3.
If you would like to know more about NETWORKDAYS formula, then visit Google Spreadsheet Documentation about NETWORKDAYS formula.
Find number of working days between two dates with custom weekends in Online Excel Spreadsheet
With NETWORKDAYS.INTL function, you would be able to find the number of working days between 2 dates. But with this function, you can specify your custom weekends.
Following is the formula for NETWORKDAYS.INTL function
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
Similar to previous formula, here start_date, num_days and holidays parameters are same. But here there is new parameter "weekend", which you can customize as per your requirement.
weekend - [ OPTIONAL - 1 by default ] - A number or string representing which days of the week are considered weekends.
String method: weekends can be specified using seven 0's and 1's, where the first number in the set represents Monday and the last number is for Sunday. A zero means that the day is a work day, a 1 means that the day is a weekend. For example, "0000011" would mean Saturday and Sunday are weekends.
Number method: instead of using the string method above, a single number can be used. 1 = Saturday/Sunday are weekends, 2 = Sunday/Monday, and this pattern repeats until 7 = Friday/Saturday. 11 = Sunday is the only weekend, 12 = Monday is the only weekend, and this pattern repeats until 17 = Saturday is the only weekend.
Following is an example which returns result as 3, because we have specified that Monday is weekend.
=NETWORKDAYS.INTL("4 July 2021","7 July 2021","1000000")
NETWORKDAYS.INTL works similarly to NETWORKDAYS but also allows weekend days to be specified (for areas where Saturday and Sunday are not considered the weekend).
If you would like to know more about NETWORKDAYS.INTL formula, then visit Google Spreadsheet Documentation about NETWORKDAYS.INTL formula.
Please share Google Spreadsheet Excel DATE Formulas with your friends and colleagues, if you find this information useful.
