Yesterday's Date

  • Hello,


    Can anyone help me put yesterday's date on an Excel Spreadsheet please ?
    When I say yesterday's date, I only want to count Weekdays.


    So for example, if I do it on a Tuesday, the formula =TODAY()-1 will give me the correct date.


    However, if I do the same on a Monday, the formula will be incorrect because it will give me Sunday's date.
    In this instance I would want the previous Friday's date.


    Thanks in advance.


    J.

  • Re: Yesterday's Date


    Hi,


    Personally I'd write a user defined function to re-work the existing weekday function on excel.


    Something along these lines

    Code
    Function workingDays(temp As Date)
    Select Case Weekday(temp)
       Case 1
          workingDays = 6
       Case 7
          workingDays = 6
       Case Else
          workingDays = Weekday(temp)
       End Select
    End Function


    You'll need to re-work this, I don't think it does exactly what you want but the methodology seems sound.


    John

  • Re: Yesterday's Date


    Why dont you use the workday function -


    =WORKDAY(B1,-1)


    This will return a date, one working day less than the date in B1.


    Do you want to return the name of the day as well?


    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Yesterday's Date


    If you want the name of the day, you can grow the workday function as follows


    =(CHOOSE(WEEKDAY((WORKDAY(B1,-1)),2),"Monday","Tuesday","Wednesday","Thursday","Friday"))


    Make sure your original date is in B1.


    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Yesterday's Date


    Well if the functions are there, I suspect they should be used... add-in or not.


    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Yesterday's Date


    Yeah, thats a fair point Norie... its bad practice to make such assumptions.


    Thanks,
    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Yesterday's Date


    Quote

    but what if they aren't there.


    and


    Quote

    perhaps because of some security issue.


    and


    Quote

    Yeah, thats a fair point


    Oh c'mon, for god's sake guys... this addin ships with Excel.


    Anyone who has not got the ability to add it need to ask serious questions of their IT department. It's not like it's 3rd party software or anything...it's on the sodding Office CD(s)....

  • Re: Yesterday's Date


    Hey mate.... this isn't a slight a you or your advice....


    but I just can't imagine who wouldn't be able to use it.... I just cannot think of a logical or rational reason for it not being available to use. ;)

  • Re: Yesterday's Date


    My department is not allowed to alter our computers in any way, including installs. Any request for additions, for the most part, go ignored. At work I am forced to use Excel 97 without even web query installed. But at home I have 2003 with anything I can get my hands on installed.

    [SIZE=2]I should change my name to STUMBED![/SIZE]

Participate now!

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