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


    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

    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.

Participate now!

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

Register Yourself Login