*If you are looking for an Excel function to get day of week from date, you've landed on the right page. This tutorial will teach you how to use the WEEKDAY formula in Excel to convert a date to a weekday name, filter, highlight and count weekends or workdays, and more.*

There are a variety of functions to work with dates in Excel. The day of week function (WEEKDAY) is particularly useful for planning and scheduling, for example to determine the timeframe of a project and automatically remove weekends from the total. So, let's run through the examples one-at-a-time and see how they can help you cope with various date-related tasks in Excel.

## WEEKDAY - Excel function for day of week

The Excel WEEKDAY function is used to return the day of the week from a given date.

The result is an integer, ranging from 1 (Sunday) to 7 (Saturday) by default. If your business logic requires a different enumeration, you can configure the formula to start counting with any other day of week.

The WEEKDAY function is available in all versions of Excel 365 through 2000.

The syntax of the WEEKDAY function is as follows:

Where:

**Serial_number** (required) - the date that you want to convert to the weekday number. It can be supplied as a serial number representing the date, as a text string in the format that Excel understands, as a reference to the cell containing the date, or by using the DATE function.

**Return_type** (optional) - determines what day of the week to use as the first day. If omitted, defaults to the Sun-Sat week.

Here is a list of all supported *return_type* values:

Return_type | Number returned |
---|---|

1 or omitted | From 1 (Sunday) to 7 (Saturday) |

2 | From 1 (Monday) to 7 (Sunday) |

3 | From 0 (Monday) to 6 (Sunday) |

11 | From 1 (Monday) to 7 (Sunday) |

12 | From 1 (Tuesday) to 7 (Monday) |

13 | From 1 (Wednesday) to 7 (Tuesday) |

14 | From 1 (Thursday) to 7 (Wednesday) |

15 | From 1 (Friday) to 7 (Thursday) |

16 | From 1 (Saturday) to 7 (Friday) |

17 | From 1 (Sunday) to 7 (Saturday) |

Note. The *return_type* values 11 through 17 were introduced in Excel 2010 and therefore they cannot be used in earlier versions.

## Basic WEEKDAY formula in Excel

For starters, let's see how to use the WEEKDAY formula in its simplest form to get the day number from date.

For example, to get the weekday from date in C4 with the default Sunday - Saturday week, the formula is:

`=WEEKDAY(C4)`

If you have a serial number representing the date (e.g. brought by the DATEVALUE function), you can enter that number directly in the formula:

`=WEEKDAY(45658)`

Also, you can type the date as a text string enclosed in quotation marks directly in the formula. Just be sure to use the date format that Excel expects and can interpret:

`=WEEKDAY("1/1/2025")`

Or, supply the source date in a 100% reliable way using the DATE function:

`=WEEKDAY(DATE(2025, 1,1))`

To use the day mapping other than the default Sun-Sat, enter an appropriate number in the second argument. For example, to start counting days from Monday, the formula is:

`=WEEKDAY(C4, 2)`

In the image below, all the formulas return the day of the week corresponding to January 1, 2025, which is stored as the number 45658 internally in Excel. Depending on the value set in the second argument, the formulas output different results.

At first sight, it may seem that the numbers returned by the WEEKDAY function have very little practical sense. But let's look at it from a different angle and discuss some formulas that solve real-life tasks.

## How to convert Excel date to weekday name

By design, the Excel WEEKDAY function returns the day of the week as a number. To turn the weekday number into the day name, employ the TEXT function.

To get **full day names**, use the "dddd" format code:

*date*), "dddd")

To return **abbreviated day names**, the format code is "ddd":

*date*), "ddd")

For example, to convert the date in A3 to the weekday name, the formula is:

`=TEXT(WEEKDAY(A3), "dddd")`

Or

`=TEXT(WEEKDAY(A3), "ddd")`

Please note that in this formula, you should use WEEKDAY with only one argument, *serial_number*. Do not include *return_type*, even if your week starts on a day other than Sunday.

Actually, the WEEKDAY function is unnecessary for this formula. The TEXT function alone would work nicely:

`=TEXT(A3, "dddd")`

Though, we often think of WEEKDAY as the day of week function, which might make this formula easier to remember.

Another possible solution is using WEEKDAY together with the CHOOSE function.

For example, to get an abbreviated weekday name from the date in A3, the formula goes as follows:

`=CHOOSE(WEEKDAY(A3),"Sun","Mon","Tus","Wed","Thu","Fri","Sat")`

Here, WEEKDAY returns a serial number from 1 (Sun) to 7 (Sat) and CHOOSE selects the corresponding value from the list. Since the date in A3 (Wednesday) corresponds to 4, CHOOSE outputs "Wed", which is the 4^{th} value in the list.

Though the CHOOSE formula is slightly more cumbersome to configure, it provides more flexibility letting you output the day names in any format you want. In the above example, we show the abbreviated day names. Instead, you can deliver full names, custom abbreviations or even day names in a different language.

For more examples, see Excel formula to get day of week from date.

## Excel WEEKDAY formula to find and filter workdays and weekends

When dealing with a long list of dates, you may want to know which ones are working days and which are weekends.

To **identify weekends and weekdays** in Excel, build an IF statement with the nested WEEKDAY function. For example:

`=IF(WEEKDAY(A3, 2)<6, "Workday", "Weekend")`

This formula goes to cell A3 and is copied down across as many cells as needed.

In the WEEKDAY formula, you set *return_type* to 2, which corresponds to the Mon-Sun week where Monday is day 1. So, if the weekday number is less than 6 (Monday through Friday), the formula returns "Workday", otherwise - "Weekend".

To **filter weekends or workdays**, apply Excel filter to your dataset (*Data* tab > *Filter*) and select either "Weekend" or "Workday".

In the screenshot below, we have weekdays filtered out, so only weekends are visible:

If some regional office of your organization works on a different schedule where the days of rest are other than Saturday and Sunday, you can easily adjust the WEEKDAY formula to your needs by specifying a different *return_type*.

For example, to treat *Saturday* and *Monday* as weekends, set *return_type* to 12, so you'll get the "Tuesday (1) to Monday (7)" week type:

`=IF(WEEKDAY(A2, 12)<6, "Workday", "Weekend")`

## How to highlight weekends workdays and in Excel

To spot weekends and workdays in your worksheet at a glance, you can get them automatically shaded in different colors. For this, use the weekday/weekend formula discussed in the previous example with Excel conditional formatting. As the condition is implied, we only need the core WEEKDAY function without the IF wrapper.

To **highlight weekends** (Saturday and Sunday):

`=WEEKDAY($A2, 2)<6`

To **highlight workdays** (Monday - Friday):

`=WEEKDAY($A2, 2)>5`

Where A2 is the upper-left cell of the selected range.

To set up the conditional formatting rule, the steps are:

- Select the list of dates (A2:A15 in our case).
- On the
*Home*tab, in the*Styles*group, click*Conditional formatting*>*New Rule*. - In the
*New Formatting Rule*dialog box, select*Use a formula to determine which cells to format*. - In the
*Format values where this formula*is true box, enter the above-mentioned formula for weekends or weekdays. - Click the
*Format*button and select the desired format. - Click
*OK*twice to save the changes and close the dialog windows.

For the detailed information on each step, please see How to set up conditional formatting with formula.

The result looks pretty nice, doesn't it?

## How to count weekdays and weekends in Excel

To get the number of weekdays or weekends in the list of dates, you can use the WEEKDAY function in combination with SUM. For example:

To **count weekends**, the formula in D3 is:

`=SUM(--(WEEKDAY(A3:A20, 2)>5))`

To **count weekdays**, the formula in D4 takes this form:

`=SUM(--(WEEKDAY(A3:A20, 2)<6))`

In Excel 365 and Excel 2021 that handle arrays natively, this works as a regular formula as shown in the screenshot below. In Excel 2019 and earlier, press Ctrl + Shift + Enter to make it an array formula.

**How these formulas work:**

The WEEKDAY function with *return_type* set to 2 returns a day number from 1 (Mon) to 7 (Sun) for each date in the range A3:A20. The logical expression checks if the returned numbers are greater than 5 (for weekends) or less than 6 (for weekdays). The result of this operation is an array of TRUE and FALSE values.

The double negation (--) coerces the logical values to 1's and 0's. And the SUM function adds them up. Given that 1 (TRUE) represents the days to be counted and 0 (FALSE) the days to be ignored, you get the desired result.

Tip. To calculate **weekdays between two dates**, use the NETWORKDAYS or NETWORKDAYS.INTL function.

## If weekday then, if Saturday or Sunday then

Finally, let's discuss a bit more specific case that shows how to determine the day of the week, and if it's Saturday or Sunday then do something, if a weekday then do something else.

*cell*, 2)>5,

*if_weekend_then*,

*if_weekday_then*)

Suppose you are calculating payments for employees who have done some extra work on their days off, so you need to apply different payments rates for workdays and weekends. This can be done using the following IF statement:

- In the
*logical_test*argument, nest the WEEKDAY function that checks whether a given day is a workday or weekend. - In the
*value_if_true*argument, multiply the number of working hours by the weekend rate (G4). - In the
*value_if_false*argument, multiply the number of working hours by the workday rate (G3).

The complete formula in D3 takes this form:

`=IF(WEEKDAY(B3, 2)>5, C3*$G$4, C3*$G$3)`

For the formula to copy correctly to the below cells, be sure to lock the rate cell addresses with the $ sign (like $G$4).

## WEEKDAY function not working

Generally, there are two common errors that a WEEKDAY formula may return:

#VALUE! error occurs if either:

*Serial_number*or*return_type*is non-numeric.*Serial_number*is out of supported dates range (1900 to 9999).

#NUM! error occurs when *return_type* is out of the permitted range (1-3 or 11-17).

This is how to use the WEEKDAY function in Excel to manipulate days of week. In the next article, we will explore Excel functions to operate on bigger time units such as weeks, months and years. Please stay tuned and thank you for reading!

## Practice workbook for download

WEEKDAY formula in Excel - examples (.xlsx file)

## 284 comments

Hello,

I am using an Excel to calculate the date for a task. I am using NETWORKDAYS(start_date, due_date) to get total project time. From there, I'm using a percentage to assign dates to all of the tasks between the start and due dates. Only issue I'm running into is that I need it to skip the weekends when assigning dates. For example, with a 10 day project turnaround, the kick-off call should happen @20% or day 2. However, if day 2 is a Saturday or Sunday, I need the calculation to use the Monday date. Is there a formula that I can insert into my calculation to skip weekends?

Hi! To add two working days to a date excluding weekends, use the WORKDAY.INTL function. The following tutorial should help: Calculating weekdays in Excel.

Pls sir, I need a formula that would calculate the total amount gotten in a week for business, thanks.

Hi! Try to follow the recommendations from this article: How to use Excel SUMIF with dates

Hi! I am trying to get the week ending of the the week day from my data however, I always end up getting "SPILL" error. Here's my formula, =IFERROR(IF(WEEKDAY(A:A)=7,7,7-WEEKDAY(A:A))+A:A,"") how should I correct this?

Hi! Read more about this error here: #SPILL! error in Excel - what it means and how to fix. Use a reference to a single cell in your formula, not the entire column. Copy this formula down along the column.

=IFERROR(IF(WEEKDAY(A1)=7,7,7-WEEKDAY(A1))+A1,"")

Hello,

I have a report that pulls the correct data, expect for the Saturday Data File. On Mondays the data is pulling Thursdays and I need it to grab Late Friday/early Saturday morning file.

If Weekday(Now()) = 2 Then

Format(Now() - 2, "yyyy") & Format(Now() - 2, "mm") & Format(Now() - 2, "dd") & ".csv

Hi! It's not an Excel formula, so I can't help. Maybe this article will be helpful: How to add and subtract dates in Excel.

Hi there, I run puppy classes and on my excel spreadsheet I would like to have a row that gives each date for a certain day of the week for the rest of the year.

For example, Saturday puppy classes, for attendance register, I want all the Saturday's for the rest of the year in a certain row. Can I do that?

Hello Michaela!

The answer to your question can be found in this article: Create a date sequence in Excel and auto fill date series.

Select the date of the first Saturday. You can set it using DATE function or use a reference to a cell with that date. Create a sequence of dates with an interval of seven days. For example:

=SEQUENCE(,20,DATE(2024,7,13),7)

Do not forget to set these cells to the date format that you want to use.

Hello,

I am working with a document that shows dates of service. I have an empty column next to it and I am searching for a formula that returns the billing week dates (Sun-Sat).

For example:

Billing Date: 5/1/2024

I am searching for a formula that will return the dates of the entire week that the billing date is from (4/29-5/4).

Thank you.

Hi! If you want a list of dates for this week, try this formula:

=A2-WEEKDAY(A2,2)+ROW(A1:A6)

If you want a text string like 4/29-5/4, convert the date to text using the TEXT function:

=TEXT(A2-WEEKDAY(A2,2)+1,"m/d")&" - "&TEXT(A2-WEEKDAY(A2,2)+6,"m/d")

Hi team,

I need a formula where brings me the days of the month based on the day of the week.

But some days of the week will repeat itself

Eg. Mon, Tue, Wed, Wed, Thu, Fri, Sat, Sat and Sun.

The formula needs to keep the same days of the months for the same days of the week and keep going. Basically doing a schedule.

Thank you!

Hi! Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data and the expected result.

It may be helpful for you to know that you can use the TEXT function to find out the name of the day of the week from the date. We have a special tutorial on this. Please see: Convert date to text in Excel - TEXT function and no-formula ways.

Hi all,

is there a possibility of getting a date from the week and day?

for example, I need to know what the date of Tuesday was last week.

If I know that last week was 20 weeks in 2024, and Tuesday is a 2-day of the week, can I get an exact date (in this example is 14.5.2024.)

thanks.

Hi! To determine the date of Tuesday of the twentieth week of the year, try this formula:

=DATE(2024,1,1)+D1*7-WEEKDAY(DATE(2024,1,1)+D1*7,12)+1 - 7*(WEEKNUM(DATE(2024,1,1)+D1*7-WEEKDAY(DATE(2024,1,1)+D1*7,12)+1)-D1)

D1 - week number

Using the DATE function, we get the first day of the year. The WEEKNUM function returns the number of the week for the date that was found and checks to see if it is the exact number that you want.

thanks Alexander, this was very helpful :D

Please help. I need to add 5 business to date time format say for example input '08-Aug-23,16:21:47 should return as '15-Aug-23,16:21:47 I tried workday but its not retiring in dd:mm:ss format

Hi! You cannot use the date format to change the date. My guess is that you use the WORKDAY function to add a few business days to the date. You can find the examples and detailed instructions here: Calculating weekdays in Excel - WORKDAY and NETWORKDAYS functions.

Need help in my formula. I need to know if shipping is on time. I have 2 order types. Guaranteed Order is next working day pickup while Regular Order has 2 business day pickup lead time. What formula should I do to know if pickupday is On Time or Late considering holidays and only Sunday as weekend. Thank you in advance.

Hi! To determine the lead time, add 2 business days using the WORKDAY function. For example:

=WORKDAY(A2, 2)

Read more: Calculating weekdays in Excel - WORKDAY and NETWORKDAYS functions.

Dear Ablebits Team,

I'm Javed, and I was reading your article on how to count weekdays and weekends in Excel. And I see the formula which you mentioned in your article.

I don't understand that formula. So I try to write my own formula and these are the formulas that I created -

(a) =LEN(TEXTJOIN(" ",TRUE,IF(WEEKDAY($B$648:$B$663,2)>5,$B$648:$B$663,"")))-LEN(SUBSTITUTE(TEXTJOIN(" ",TRUE,IF(WEEKDAY($B$648:$B$663,2)>5,$B$648:$B$663,""))," ",""))+1

(b) {=SUM(N(WEEKDAY(B648:B663,2)>5))}

(c) {=SUM(IF(WEEKDAY(B648:B663,2)>5,1,0))}

And these 3 formulas give me right answers. And now, I want your clarification on these formulas.

I have maintained monthly calendar in excel for my daily attendance with following details.

Column "A" is date, "B" is weekday (mon., tue., till sunday), "C" is hours spent in office, "D" is total hours claimed. To sum weekly hours of column "C" and "D", I put manual formula in front of each Sunday in column "E" & "F".

e.g. if my month day starts from Friday (1.12.23) then I put manual formula in from of Sunday (3.12.23) to sum three days hours. and then for next weekly sum I put manual formula on next Sunday (starting from Monday 4.12.23 till 10.12.23) and so on.

Now, I need your expert help to avoid this manual work. If you can help me preparing formula for this it will be great help. Formula I am looking which calculates weekly total hours automatically in front of each Sunday.

Thanks for your help and time in advance.

Hello! If I understand your task correctly, try to enter the following formula in cell E2 and then copy it down along the column:

=IF(WEEKDAY(A2,2)=7, SUMIFS($C$2:C2, $A$2:A2, "<="&A2, $A$2:A2,">="&(EOMONTH(A2,-1)+1)) - SUM($E$1:E1), "")

Use the WEEKDAY function to define the day of the week on which the counting will be done. The EOMONTH function helps to define the date range from the beginning of the month to the current day. Use these criteria in the SUMIFS function.

Thanks a lot for your time and reply... much appreciated….

God bless you...

I have a list of personal leave taken over the last 2 years for all employees and need to know what day or days of the week the leave was for. Any help would be appreciated.

To understand what you want to do, give an example of the source data and the expected result.

Hi, Need help for below:

I would like to create the formula in a single cell (I do not want to add column for weekday/ weekend identification) which will check if particular date is weekday or weekend, and if a weekday it will populate specific number for e.g 10 (Specific columns have this value) or else it will populate 0.

Pls suggest the logic for this.

Hi! To weekday / weekend identification, use this guide: Calculating weekdays in Excel - WORKDAY and NETWORKDAYS functions.

I am trying to make a employee schedule with rotating weekends off and every employee gets one weekend off a month.I need to have four people working and one employee off on the weekends. This needs to be done for three shifts. I am trying to use this formula but it has errors:

=IF(OR(WEEKDAY(B$1)=1,WEEKDAY(B$1)=7),"Working","Off"). How can I correct this? Thank you for your time

Hi! It is not possible to create a monthly work schedule for 4 employees with one formula. Describe what result you wanted to get with this formula.

Hi,

Really need your help with a formula/s.

In a production environment we start week on Monday at 6am (06:00:00) and finish at 4pm (16:00:00), then start back up next day at 06:00.

We run Mon - Thurs, but for planning we don't include Fri, Sat or Sun

I need a formula that will start production at 6am run through til 4pm, if the production time is greater than 10hrs block out the completion date and continue from 6am tues etc etc to give an expected completed date. I'll add in 10 mins for a change over and the the above again.

Also anything that falls on F, S or S will move to 6am Monday

Capacity

Mon - Thur 6:00am - 4:00pm (Break times 1hr & 5 mins per day) Total Production time available is 8 hrs 55 mins)

Fri, St, Sun - Not running

Maybe I have written it to be too complicated, please let me know if it is and any help is greatly appreciated

Hi!

If I understand your task correctly, the following formula should work for you:

=WORKDAY.INTL(A1, INT((B1/24-(TIME(8,55,0) - (A1-INT(A1)-TIME(6,0,0)))) / TIME(8,55,0)),"0000111") + B1/24-(TIME(8,55,0)-(A1-INT(A1)-TIME(6,0,0))) - INT((B1/24-(TIME(8,55,0) - (A1-INT(A1)-TIME(6,0,0))))/TIME(8,55,0))*TIME(8,55,0) + TIME(1,5,0)+TIME(6,0,0)

A1 - start date&time

B1 - hours

You can learn more about WORKDAY.INTL function in this article: Calculating weekdays in Excel - WORKDAY and NETWORKDAYS functions.

I'm looking for a formula :

Allocation of tasks duration Hours .

6 Working day are from Monday 6 am to Sunday 6 am .

1 Week off day is from Sunday 6 am to monday 6 am .

Per day hour is 24 in that utilize hours are 19.35 hours . Break hours 4.65 hours considering by day.

[ in each utilize hours is 6.45 and Break hours is 1.55 ]

Working in 3 shifts pattern .

1 st shift morning 6 am to 2 pm

2 nd shift 2 pm to 10 pm

3 rd shift 10 pm to 6 am ( next day )

Calculate finish date and time each tasks ..

Ex .

In column A : A2 cell having project start date & Time : 01.01.2024 6 am

In column B :

Cell B2

Task 1 :

B3

Task 2 :

Goes on ..

In column C

Having each tasks completion Hours respectively In cell C2 , C3 etc ..

In column D

Need to calculate

Task completion/ finish date and Time .

By applying condtions in week day and week off hours

For task 2 start date will be completion date of task 1

So please help ..

Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.

workday.intl( start date , days , week end , Holidays )

in general addition formula

Task Name Task Duration in Hours Task Start Date & Time Task Finish Date & Time

Task 1 18 01-01-2024 02:00 PM 02-01-2024 08:00 AM

finish date & time getting is correct.

used simple formula : Task Start Date & Time + ( Task Duration in Hours / 24 )

is it possible get correct answer as above by using Formula workday.intl( start date , days , week end , Holidays )

Task Name Task Duration in Hours Task Start Date & Time Task Finish Date & Time

Task 1 18 01-01-2024 02:00 PM 01-01-2024 12:00 AM

02-01-2024 08:00 AM 01-01-2024 12:00 AM

and weekend : 1 , 2 , 3 etc but my weekend lies between 2 days half / hlaf ; [ sunday 6 AM to Monday 6 AM ]

Return_type Number returned

1 or omitted From 1 (Sunday) to 7 (Saturday)

2 From 1 (Monday) to 7 (Sunday)

3 From 0 (Monday) to 6 (Sunday)

11 From 1 (Monday) to 7 (Sunday)

12 From 1 (Tuesday) to 7 (Monday)

13 From 1 (Wednesday) to 7 (Tuesday)

14 From 1 (Thursday) to 7 (Wednesday)

15 From 1 (Friday) to 7 (Thursday)

16 From 1 (Saturday) to 7 (Friday)

17 From 1 (Sunday) to 7 (Saturday)

please help

Hi! The WORKDAY.INTL function and other functions to count working days does not work with time. You can only count whole days.

Thanks !!

I have flights on Mondays, Wednesdays, Fridays and Sundays. I wanted to be able to list dates in a month that corresponds to these days. The easiest way to do is to write them manually from calendar but that is time consuming. Is there a single formula that enable me list dates of all Mondays, Wednesday, Fridays and Sundays in a specific month or ranges of months?

Hello!

Use the recommendations from this article: Create a date sequence in Excel and auto fill date series

I hope this formula works for you:

=DATE(2023,1,1)+SEQUENCE(120,1,1,2)+CEILING(SEQUENCE(120,1,0,-4)/4,4)/4

I am currently using a formula to determine a deadline adjustment based on the number of days a project is ahead or behind schedule.

the formula I'm using is, and this will give me a realistic end date for the project.

=SUM (O10+-W7)

Where O10 is the cell that has an original date the project is supposed to end, and W7 is the cell that has formula to count the number of days the project is ahead/behind schedule.

Is there a way to add to the "=SUM(O10+-W7)" formula so that if the date that is calculated lands on a weekend, it'll automatically move the end date to the next scheduled weekday?

Hello!

You can add or subtract from date the desired number of working days including weekends and holidays with the WORKDAY.INTL function For example,

=WORKDAY.INTL(O10,W7,1)

I hope it’ll be helpful.

Alexander, you ROCK! The update works as it should.

Thank you so much!

Need please help. Not sure which formula or conditions to include. I have a date 3/25/2023 thru 5/30/2023. I need to count 6 weeks out from 3/25/2023 which would be 5/6/2023 but in workdays to achieve the result in date format.

Hello!

Use the WORKDAY function to find the first working day after a date.

=WORKDAY(D1+C1*7-1,1)

D1- start date

C1 - weeks

How do I add 7 days to a column.

eg:

27.03.23

03.04.23

10.04.23

etc.

Thanks. New to Excel.

Hi!

The following tutorial should help: How to add and subtract dates, days, weeks, months and years in Excel.

Try formula: =A1+7

How can lieu dates for Stat holidays that fall on a weekend be calculated? For example, in 2023, January 1st falls on Sunday and Nov 11th falls on Saturday. If a holiday is on Sunday, it needs to add 1 day and if it's on a Saturday, it needs to add 2 days. Here's my two novice unsuccessful attempts at this formula where D14 is the stat holiday date:

=IF(WEEKDAY($D14,2)=7,$D14+1)=IF(WEEKDAY($D14,2)=6,$D14+2)

This returns True if it's a weekday and False if it's a weekend but I want it to return the new date if it's on a weekend and the same date if it's on a weekday.

=IF(WEEKDAY($D14,2)>5),WORKDAY($D14,2)

This alternate option is not working at all.

Thanks in advance for your help.

Hi!

To determine the first business day after a specified date, use the WORKDAY function.

=IF(WEEKDAY($D14,2)>5,WORKDAY($D14,1),D$14)

That works with a minor edit at the end of your solution ($D14).

Thank you so much for helping out a nurse that's trying to create a customized team vacation calendar. So generous of you to share your expertise.

I have created a project schedule with conditional formatting to show week day and weekends - which works perfectly. Each task has a set duration (of workdays) before the final product. I currently have it formatted and coded so I input the due date and it plans backwards using the duration's set for each task.

This however does not exclude weekends, how do I use excel to automatically update the number of days if a weekend falls in that space?

IE if a task takes 5 days but day 3 and 4 are weekends the task would update to take 7 days? Is this even possible?

My aim is to then use that data as a bar graph so the schedule automatically grows/shrinks based on the user inputted requested end date.

Hello!

To add days to a date, including weekends, use the WORKDAY and NETWORKDAYS functions.

This should solve your task.

Request your help in below scenarios

1. If my month start with Monday then from Monday to Sunday to be marked with Week1 Next Monday to Sunday will be Week2 next Monday to Sunday will be Week3 next from Monday to till end of month to be marked as Week3

2. If month doesn't start with Monday then from day 1 to 2nd Sunday of the month to be marked as week1 next Monday to Sunday to be marked as week2 next Monday to Sunday to be marked as week3 next Monday to end of the month as week4

I'm SUPER new to this!

I'm trying to figure out how to show a Weekday, Month, Day of the Month, & Year (Wednesday July 13, 2022) by entering 7/13/22 in a cell.

I have it in one now. But if I copy & paste it, it shows the date that I copied. I need it to be blank, so I can enter a numeric date, then it transposes it to the format I mentioned above.

Something for a cell to select an AM/PM time of day too, would be appreciated!

PLEASE & THANK YOU!

Hello!

Enter the date in the cell and then set the custom date format as you need. You can find the examples and detailed instructions here: How to change Excel date format and create custom formatting. I hope it’ll be helpful.

Good day.

I would like to count the days in a range.

eg.

(a1) july 7, 2022 - (a2) july 9, 2022

(b1) july 8, 2022 - (b2) july 10, 2022

Results should be:

Thursday - 1

Friday - 2

Saturday - 2

Sunday - 1

Thanks

Hi!

You want to count the days of the week when only the first and last date in a date range is specified. I'm really sorry, looks like this is not possible with the standard Excel options.

Hello!

Seeking your kind assistance. I need to separate the sum of the Monday to Friday sales from the weekend sales per week of the month for the whole year. Is there an easier way to do this other than sum function?

Hi!

To find a conditional sum, use the SUMPRODUCT function.

=SUMPRODUCT(--(WEEKDAY(A1:A100,2)<6),B1:B100)

I hope my advice will help you solve your task.

Thank you Mr. Trifuntov!

Be careful!

=DAYS(01/07/2022,01/08/2022) gives 31, the number of days in July, so this is BETWEEN the dates: 01/08/2022 not being counted.

=NETWORKDAYS(01/07/2022,01/08/2022) gives 22. that is the number of workdays in July 2022 plus the Monday 1 August 2022. So, in the NETWORKDAYS (and NETWORKDAYS.INTL) the end-date is included in the period.

Be aware of this if you have to calculate the workdays in a period as a fraction of the number of days in that period, this can be dealt with by simply adding 1 to the DAYS function.

Hi!

Keep in mind that the date 01/08/2022 actually means 01/08/2022 00:00:00 in Excel and does not include that day. We have written about this many times in our blog.

Thanks, but that's not really the issue here (and indeed well known); the issue is that the NETWORKDAYS function does include the end-date in the evaluation of workdays, which at least is inconsistent with the DAYS function.

Hi!

It is not possible to set an Excel filter on the days of the week in the current column.

You can use WEEKDAY and FILTER function to get a list of dates in a new place.

Alternatively, use an additional column with the WEEKADY function to set a filter on that column.

Sorry, your reply does not relate to my point.

I have the below calendar formula and have been searching for days and days to insert a ceiling function to highlight every other Friday and have not been successful. "P9" is a cell that has "July 1, 2022" in it. Please help. Thanks.

=TEXT(SEQUENCE(54,7,-MOD(WEEKDAY(P9,2),7)+1)," [>"&DAY(EOMONTH(P9,0))&"];;#")

Hi!

I don't really understand what you want to do. To get the July calendar, try changing 1 to 2 in the formula. If this is not what you wanted, please describe the problem in more detail.

Hi, I would like to exclude weekend in my column, instead of i filter out weekend and weekday in new column, is there any ways to just filter in those column

Hi!

In cell A1, write the starting date. In cell A2, write the formula and copy it down along the column:

=IF(WEEKDAY(A1+1,2)>5,A1+3,A1+1)

Hope this is what you need.

I have a spreadsheet with SUNDAY-SATURDAY tabs at the bottom for each day. I would like to be able to put the date in the SUNDAY tab and it auto-populate the date for the remaining 6 tabs (Mon-Sat). Is that possible? I've tried a few different formulas and can't figure it out.

I am currently just going to each tab and changing the daily dates each week. It would be nice to just change Sunday and it populate the rest for me.

Thanks!

To be more clear... I am not trying to change the name of the tabs itself. I have a cell in each tab for that days date that I want to auto-populate.

Hi!

Here is the article that may be helpful to you: Adding days to a date in Excel.

Thanks! This was exactly what I needed! I was making it way more difficult than just =SUN!C1+1 for the date cell in the Monday tab, then +2 for the cell in the Tuesday tab, etc..

Hello!

I don't quite understand where you want to put the date. If the date is written in a cell, then every next day means adding 1 to the date. If you want to change tab names, you can only do it manually or use a VBA macro.

Is there say week 18 and Tuesday shown as 18.2 and plus 4 days show 18.6. After 18.7 next is week 19. Any formula to do this.

Hello!

Use WEEKNUM and WEEKDAY function:

=WEEKNUM(B1)&"."&WEEKDAY(B1,2)

This should solve your task.

Please help. I track shipments per week of the year (week Num). The current capacity is shipping two orders per week. If there are more than two orders per week listed, what formula do I need so the orders greater than two are moved into the following week?

Thanks for your help.

Hello!

In a separate column, determine the week number using the WEEKNUM function. Then use the COUNTIF function to count the number of times the desired week number occurs.

I hope my advice will help you solve your task.