WEEKDAY function in Excel

Excel Date and Time Functions return a dynamic date or time in a cell.

How to calculate next day of week in Excel

To return the next specific day of week (i.e. the next Wednesday, or Friday, or Monday) with a given start date, you can use a formula based on the WEEKDAY function. Formula =date+7-WEEKDAY(date+7-dow) Explanation  In the example shown, the formula in D6 is: =B6+7-WEEKDAY(B6+7-2) Where B6 contains the date Friday, January 16, 2015, and 2 represents Monday, the “dow”…. read more »

How to calculate nth day of week in month in Excel

To get the nth day if week in a month (i.e. the first Tuesday, third Tuesday, forth Thursday, etc.) you can use a formula based on the DAY and WEEKDAY functions. Formula =date-DAY(date)+1+n*7-WEEKDAY(date-DAY(date)+8-dow) Note:  Day of week is abbreviated “dow” in the generic form of the formula. This is a number between 1 (Sunday) and 7… read more »

Get work hours between dates custom schedule in Excel

To calculate work hours between two dates with a custom schedule, you can use a formula based on the WEEKDAY and SUMPRODUCT functions, with help from ROW, INDIRECT, and MID. Formula =SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&”:”&end))),1) *ISNA(MATCH(ROW(INDIRECT(start&”:”&end)),holidays,0))) Explanation In the example shown, the formula in F8 is: =SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&”:”&C6))),1) *ISNA(MATCH(ROW(INDIRECT(B6&”:”&C6)),holidays,0))) Which returns 36 hours, based on a custom schedule where… read more »

Roll back weekday to Friday base on a particular date in Excel

Case study: If Monday, roll back to Friday To check the weekday of a date, and roll back to Friday when the date is a Monday, you can use the IF and WEEKDAY functions. Formula =IF(WEEKDAY(date)=2,date-3,date) Explanation In the example shown, the formula in C5 is =IF(WEEKDAY(B5)=2,B5-3,B5) How this formula works The WEEKDAY function returns… read more »

Generate series of dates by weekends in Excel

This tutorials covers how to generates Series of dates increment by weekends from a single start date in Excel. If need to generate a dynamic series of dates with a formula that include only future weekend dates (i.e. Sat and Sun), you can do so with a formula that uses the IF and WEEKDAY functions. Formula… read more »

Calculate series of dates by workdays in Excel

This tutorials covers how to generate series of dates increment by workdays from a single start date in Excel If need to generate a dynamic series of dates that are workdays only (i.e. Monday through Friday), you can do so with a simple formula that uses the the WORKDAY function Formula =WORKDAY(date,1) Explanation   In the… read more »

Custom weekday abbreviation in Excel

This tutorial shows how to create custom weekday abbreviation in Excel using example below. To create a custom weekday abbreviation, you can use a formula based on the CHOOSE and WEEKDAY functions. With this approach, you can generate a custom one-letter abbreviation, two-letter abbreviation, or any weekday that makes sense in your particular situation. Formula =CHOOSE(WEEKDAY(date),”S”,”M”,”T”,”W”,”T”,”F”,”S”)… read more »

Count day of week between dates in Excel

This tutorial shows how to Count day of week between dates in Excel using example below. To count the number of Mondays, Fridays, Sundays, etc. between two dates you can use an array formula that uses several functions: SUMPRODUCT, WEEKDAY, ROW, and INDIRECT.  Formula =SUMPRODUCT(–(WEEKDAY(ROW(INDIRECT(date1&”:”&date2)))=dow)) Explanation In the example shown, the formula in cell E6… read more »

Steps to create Dynamic calendar grid in Excel

This tutorial show how to  create Dynamic calendar grid in Excel using the example below. You can set up dynamic calendar grid on an Excel worksheet with a series of formulas, as explained in this article. Explanation of how this formula works In the example shown, the formula in B6 is: =start-CHOOSE(WEEKDAY(start),0,1,2,3,4,5,6) where “start” is the named… read more »

Get last weekday in month in Excel

This tutorial shows you how to find the last weekday of the month in Excel. To get the last weekday in a month (i.e. the last Saturday, the last Friday, the last Monday, etc) you can use a formula based on the EOMONTH and WEEKDAY functions. Formula =EOMONTH(date,0)+1-WEEKDAY(EOMONTH(date,0)+1-dow) Explanation First, this formula determines the first… read more »

Sidebar