# Excel Experts - Formula for Finding Weekend in the List of Days/Dates



## laxtheleo (Jul 8, 2008)

HI,

I would like to know if there is a Formula in Excel for finding the weekend from the given list of dates and Automatically subtracting them and calculating the next weekday?

Eg. If I receieve a work Request on Friday and it has a deadline of 48 hrs, then i would like a formula that should automatically pick the Deadline day/date as Monday instead of Saturday. (It should not consider Weekend days.) 

Hope I was clear enough in my query !

thanx for your help.
Lax


----------



## The Villan (Feb 20, 2006)

There is a formula called Weekday which returns a number based on the day of the week. So together with an if statement you could ask excel to look at a date and determine which day of the week it is 1 = Sunday, 2 = Monday and so on. For example

=If(weekday(A1+B1,7),A1+B1+2,if(weekday(A1+B1,1),A1+B1+1,A1+B1))

Where 7 is Saturday and 1 is Sunday

Got to go for dinner otherwise I would put you the correct formula. I am sure somebody will help whilst I am away.


----------



## The Villan (Feb 20, 2006)

Had time to think
Have a look at attached spreadsheet
Green cells are input cells
Rose coloured cells contain formulas


----------



## The Villan (Feb 20, 2006)

Anybody in the right mood, will I am sure improve on that.


----------



## Aj_old (Sep 24, 2007)

I think you need workday function not, something with weekend


----------



## Aj_old (Sep 24, 2007)

for example:

```
=WORKDAY(A4;C4)
```
, where: A4 - i the date you recive the work request, and the C4 - the due in days(for example 2 for 48 hours)


----------



## The Villan (Feb 20, 2006)

The weekday function is in D4 and Excel returns a number that equates to what the day number is.
1 - Sunday
2 = Monday
...
7 = Saturday

So the formula in D4 looks at C4 and returns a number based on what day it is.

E4 uses an If statement to tell Excel what to do if a 1 or 7 is returned, like add an extra 2 days or 1 day onto the result.


----------

