NON WORKING DAYS

Hi all,


I had a formula working before that uses TODAY(-1), that was automatically not including weekends (non-working days on admin account settings). Over this weekend, this has now broken, without changing any settings on our account. Can anyone advise please as this is quite critical to our figures... is there any development working going on that anyone is aware of, that would have changed the formula being amended automatically?


Thanks,


Meg

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @megan.griffiths

    The Today function will find Today's date, and then if you use numbers within it, such as TODAY(-1), it sees those numbers as days (not working days). There has been no change to this functionality recently; could the change that you're seeing have to do with when you are opening and saving the sheet?

    TODAY() requires the sheet to be opened and saved for it to recognize the date (see the Help Center article here).

    If you have a formula that atuomatically needs to exclude weekends we'd be happy to help you with this creation, but it would be helpful to know more information. Can you copy/paste your current formula and explain exactly what it is you're looking to do?

    For example, if you are looking to COUNT how many rows came in on a working day that is one working day ago, you could use the WEEKDAY function to find out what day of the week TODAY is, then either minus 3 days if it's Monday, or minus 1 day if it's any other day.

    =IF(WEEKDAY(TODAY()) = 2, COUNTIF(Date:Date, <TODAY(-3)), COUNTIF(Date:Date, <TODAY(-1))) 

    Does this make sense?

    Cheers,

    Genevieve

  • megan.griffiths
    megan.griffiths ✭✭✭✭

    Hi Genevieve,


    Thanks for your response.


    The current formulas that we are using are:

    YESTERDAY:

    =SUMIFS({Fee £}, {Date Received}, TODAY() - 1) 

    TODAY:

    =SUMIFS({Fee £}, {Date Received}, TODAY())

    on both of these, we are also removing any where the {Case Owner} is “Return to Pod”, but the <> function was not working, therefore I created a work around as below:

    =SUMIFS({Fee £}, {Date Received}, TODAY(-1), {Case Owner}, "Return to Pod")

    minus

    =SUMIFS({Fee £}, {Date Received}, TODAY() - 1)

    equals final total.


    The ‘error’ that we are getting, is that on a Monday, the total for ‘Yesterday’ is coming in a £0, as it is looking at Sunday’s data. We basically want to exclude weekends. 


    Your formula makes total sense, I'm just not sure how to incorporate the WEEKDAY function into a SUMIFS equation?


    Many thanks again for your help,


    Meg

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @megan.griffiths

    No problem, we can definitely add this in to your formula! Essentially the IF statement just looks to see if Today is Monday or not... so we'll need to repeat your formula: once if Today is Monday, and a second time if Today is NOT Monday.

    Ex.

    =IF(WEEKDAY(TODAY()) = 2, (Formula -3), OTHERWISE (Formula -1))

    So for your Formulas:

    YESTERDAY:

    =IF(WEEKDAY(TODAY()) = 2, SUMIFS({Fee £}, {Date Received}, TODAY(- 3)), SUMIFS({Fee £}, {Date Received}, TODAY(-1)))


    TODAY:

    The Today formula should be the same, as TODAY will always look at Today's date. If you open the sheet on Saturday or Sunday it will show 0, but then when you open it again on Monday you'll see Monday's data.


    COMBO:

    Then for your formula where you are taking off "Return to Prod" it would look like this:

    =IF(WEEKDAY(TODAY()) = 2,

    If it's Monday:

    SUMIFS({Fee £}, {Date Received}, TODAY(-3), {Case Owner}, "Return to Pod") - SUMIFS({Fee £}, {Date Received}, TODAY(-3)),

    If it's NOT Monday:

    SUMIFS({Fee £}, {Date Received}, TODAY(-1), {Case Owner}, "Return to Pod") - SUMIFS({Fee £}, {Date Received}, TODAY(-1)))


    Full Formula:

    =IF(WEEKDAY(TODAY()) = 2, SUMIFS({Fee £}, {Date Received}, TODAY(-3), {Case Owner}, "Return to Pod") - SUMIFS({Fee £}, {Date Received}, TODAY(-3)), SUMIFS({Fee £}, {Date Received}, TODAY(-1), {Case Owner}, "Return to Pod") - SUMIFS({Fee £}, {Date Received}, TODAY(-1)))


    Let me know if this works for you!

    Cheers,

    Genevieve

  • megan.griffiths
    megan.griffiths ✭✭✭✭

    Genevieve, you're a genius!


    Thank you so much for your help, this has worked perfectly! Fingers crossed it works on Monday, I will let you know and then this thread can be closed!


    Many thanks again,


    Meg

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @megan.griffiths

    I'm glad I could help! 🙂

  • megan.griffiths
    megan.griffiths ✭✭✭✭

    Hi Genevieve,


    Are you able to also build the weekends into that formula please? I've had a go but I am struggling:

    =IF(WEEKDAY(TODAY()) = 2, COUNTIF({Date Received}, TODAY(-3)), IF(WEEKDAY(TODAY()) = 1, COUNTIF({Date Received}, TODAY(-2)), IF(WEEKDAY(TODAY()) = 7, COUNTIF({Date Received}, TODAY(-1), IF(WEEKDAY(TODAY()) = 4, COUNTIF({Date Received}, TODAY(-1), IF(WEEKDAY(TODAY()) = 3, COUNTIF({Date Received}, TODAY(-1)), IF(WEEKDAY(TODAY()) = 5, COUNTIF({Date Received}, TODAY(-1)), IF(WEEKDAY(TODAY()) = 6, COUNTIF({Date Received}, TODAY(-1)))))))))))


    So I need to show Friday as yesterday on Saturdays, Sundays AND Mondays...

    I've tried to build as above, where all days are -1, other than Sundays (-2), and Mondays (-3) but it has come back today with a blank cell value:


    Any idea where I'm going wrong?


    Thanks,


    Meg

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @megan.griffiths

    I see you posted about this and Paul answered you here - I agree with his assessment! Looks like you might be missing some of these closing parentheses: )

    =IF(WEEKDAY(TODAY()) = 2, COUNTIF({Date Received}, TODAY(-3)), IF(WEEKDAY(TODAY()) = 1, COUNTIF({Date Received}, TODAY(-2)), IF(WEEKDAY(TODAY()) = 7, COUNTIF({Date Received}, TODAY(-1)), IF(WEEKDAY(TODAY()) = 4, COUNTIF({Date Received}, TODAY(-1)), IF(WEEKDAY(TODAY()) = 3, COUNTIF({Date Received}, TODAY(-1)), IF(WEEKDAY(TODAY()) = 5, COUNTIF({Date Received}, TODAY(-1)), IF(WEEKDAY(TODAY()) = 6, COUNTIF({Date Received}, TODAY(-1)))))))))

  • Bryan Mac
    Bryan Mac ✭✭
    edited 10/07/22

    This would be a nice feature in smartsheets without using a function with national holidays included. Also some factories shut down for a week or two every year I'd like to be able these dates for each different manufacture individually. You might guess I'm tracking materials/products

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!