# Help,date formulas

I am trying to make a wroksheet that will automatically update the date a letter needs to be answered by i.e. 3 working days. I have the formula correct but can anyone tell me how to get excel to NOT include sat/sun as a working day?? Is this possible

Thanks

Lynda
:puzzled:

• Hi Linda

Welcome to the board!

Not too sure of you exact needs, but perhaps this formula will help?

Let's say you have a date in cell A1 and you want to always add the correct number of days so the result is the next Working day. Try this formula

=IF(WEEKDAY(A1)=6+N("It's Friday"),A1+3,IF(WEEKDAY(A1)=7+N("It's Saturday"),A1+2,A1+1))

The use of the N function is just so you can see what the WEEKDAY function returns for Friday and Saturday. For Sunday it would return 1

• Hi Thanks for that formula but that doen't seem to work either, and i have tried varying it etc. I will try to explain clearer what I am trying to do.

Cell A1 has the date that a letter has been recieved, for example today it would have 24/02/03, in cell G1 i would like it to automatically say which date the letter must be replied within which is 3 working days, so it should be 28/02/03. This is okay until i put in a letter that came in on say 27/02/03, it returns a reply date of 02/03/03 which is a Sunday and the office isn't open on a Sunday! How can I get the formula to recognise sat/sun and add on two days if required.

Does this make sense??

Thanks

Lynda

• Hi Linda

Can you not just work on Saturday and Sundays

Seriously though, try this one

Code
``=A1+CHOOSE(WEEKDAY(A1),3,3,3,5,5,5,4)``
• Lynda,

there is a function that comes with the Analysis Toolpak add-in that does exactly what you want called WORKDAY. To load the add-in go to the Tools menu and select Add-Ins... and check off the Analysis Toolpak and Analysis Toolpak VBA boxes. Then in the Date & Time category you will find WORKDAY. It automatically skips weekends and optionally any holidays you supply.

• A little more archaic but I also use MOD to find day of the week.

For applications like comparing all mondays or such I do something like hide all columns except for ones who's mod of 7 are the same.

Code
``````If Not ActiveCell Mod 7 = day_variable Then
Selection.EntireColumn.Hidden = True
column_to_hide = ActiveCell.Column
End If``````

Mod also works in formula's on a spread sheet.

• Thank you all so much for your help in this I now have it working.

Thanks

Lynda

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!