Help,date formulas

  • help please anyone

    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



  • 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??



  • 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.

    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.

Participate now!

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