NON WORKING DAYS

megan.griffiths

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

Genevieve P.

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

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.

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

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.

Hi @megan.griffiths

I'm glad I could help! 🙂

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.

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

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