Formula to calculate the previous & current week's date based on current week's Monday

Options

Please help me with the formula to get the date and day under following condition:

If Today is Monday then it should reflect last week's date from Monday to Friday (date and day), if Today is anything other than Monday then it reflect current week date and day from Monday to Friday.

Currently I am using following formula:

=IF(WEEKDAY(Date1) = 1, Date1 - (WEEKDAY(Date1) - 2), Date1 - (WEEKDAY(Date1) - 2) + 0)

This formula changed every week

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @sandeep kumar

    Would you be able to explain what it is you're looking to do a little further?

    When you say "date and day", what is it that you're looking to output? Is the screen capture above the example data you're looking at, or is it an example of what you want it to show?

    The current formula will always show you that week's Monday Date. You can use the TODAY Function instead of referencing a Date Cell if that would help:

    =IF(WEEKDAY(TODAY()) = 1, TODAY() - (WEEKDAY(TODAY()) - 2), TODAY() - (WEEKDAY(TODAY()) - 2) + 0)

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!