Solved How to use the if command to filter weekends

January 4, 2017 at 08:49:13
Specs: Windows XP
I am a Developer, I want you to develop a Leave template to help show:-

Leave Starts --Date ---
No. of Days Applied for ---4-----
Leave Expires ----Date----
Resumption Date ----Date ----

In the case where resumption date is a weekend, I want you to build in a formula to move resumption date to the next working day

Thank you.

Vincent Asenime Ebo
(Nigeria)


See More: How to use the if command to filter weekends

Report •

#1
January 4, 2017 at 10:16:54
Look at the WORKDAY() function.
"Use this function to calculate a past or future date based on a starting date and a
specified number of days. The function excludes weekends and holidays."

So with your data like:

              A                      B                C
1)       Leave Start            # of Days        Leave Expires
2) Wednesday, January 04, 2017      4       Tuesday, January 10, 2017


In cell C2 enter the formula:

=WORKDAY(A2,B2,X1:X6)

The cell X1:X6 are Dates for any Holidays you wish to include.

See how that works for you.

There is also a WORKDAY.INTL() which lets you specify what are "weekends" if Saturday & Sunday are not considered the weekend in you area.


And finally, Excel questions are best asked in the Office Software forum.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
January 4, 2017 at 10:58:02
✔ Best Answer
I should have mentioned that the WORKDAY() function does NOT count the first date.

So in the example

              A                      B                C
1)       Leave Start            # of Days        Leave Expires
2) Wednesday, January 04, 2017      4       Tuesday, January 10, 2017

The days are counted as:

1) Thursday January 05, 2017
2) Friday January 06, 2017
3) Monday January 09, 2017
4) Tuesday January 10, 2017

If need be, there are ways to modify this behavior.

MIKE

http://www.skeptic.com/


Report •
Related Solutions


Ask Question