Formula that returns a value equal to every other day

Dawn McCallister

Date formulas are the most challenging for me. The requirement is to provide a visual calendar that will trigger the quality audit team to complete the audits expected for the current business day. The sheet has three frequency options: Daily, every other day, and weekly. I am struggling to determine the formula for every other day frequency selection. The formula would need to exclude weekends.

I have determined the formulas for Weekly (Every Friday) and daily. Once I have the third formula, I will be able to wrap things up with an IF/Then statement to run the correct formula based on the frequency option selected.

We are populating a due date and not using workflow because the process owner wants a calendar view as the visual trigger.

Your help is greatly appreciated.

Dawn

Jason Tarpinian

If you set a sheet up like this using the RowID column to number rows for you, you can use the following formula to calculate every other day ("Start Date" is used as an arbitrary starting point, but you need some start date at minimum).

=WORKDAY([Start Date]$1, [Row #]@row * 2)

This will get your calendar to look like this:

Then, if you need the current/next frequency date, use the following formula:
=INDEX([Every Other Day Excluding Weekends]:[Every Other Day Excluding Weekends], MIN(COLLECT([Row #]:[Row #], [Every Other Day Excluding Weekends]:[Every Other Day Excluding Weekends], >=TODAY())))

Paul Newcome

Try

WORKDAY([Last Audit Completed]@row, 2)

Dawn McCallister

@jason tarpinian @Paul newcome

Both of your solutions would work but I should have defined the requirement better. The first task identified to be completed every other day is "Inside Woodgrain Embossing Checks". If we kick this new process off on Monday (1/27/2025), the due date would be Wednesday (1/29/2025). When Wednesday (1/29/2025) was equal to today, then the due date on the same row would update to Friday (1/31/2025). When Friday (1/31/2025) was equal to today, then the due date on the same row would update to Tuesday (2/4/2025).

I hope this helps

Paul Newcome

Is it not based off of the [Last Audit Completed] then? How do you establish the "kick-off" date?

Dawn McCallister

@paul newcome

You are correct, is it not based on the last audit date. Row #1 (a helper row) has the kick-off date - not a fan. However the owner of the process does not want a due date based on the audit complete. The next audit due notification should kick off regardless if the previous one was completed.

It should be noted. I am open to a better solution, my brain just can't find one that gives both the calendar view. I initially was going to use workflows to kick off notifications.

Paul Newcome

Try this:

=[Due Date]1 + IF([Start Date]@row < TODAY(), ABS(CEILING(TODAY() - [Due Date]1, 2)))

Ipshita

Hello @Dawn McCallister here is a simple formula for you to help calculate date for every other day -

=DATEONLY([Date From]@row + 2)

I have used this formula in the below due date column based on the date from column.. this is also a formula column that is calculating today's date, now you can use any date column in your sheet and this formula will calculate every other day from that date column.

Hope this helps!

Best,

Ipshita

Ipshita

@Dawn McCallister

I apologize to have overlooked that you only wanted to find due dates excluding the weekends, in such case the formula should be this -

=WORKDAY([Date From]@row, 2)

Hope this helps,

Thanks:)

Ipshita

Paul Newcome

@Ipshita I don't think that quite meets the needs of what essentially amounts to a rolling 2 day interval. Once the [Due Date] is reached, it needs to go out another two days. In a previous comment, the poster indicated that just the basic WORKDAY function doesn't quite work because they need it to continuously increment.

Having said that…

@Dawn McCallister I did forget to incorporate skipping weekends. This should do the trick:

=WORKDAY([Due Date]1 + IF([Start Date]@row < TODAY(), ABS(CEILING(TODAY() - [Due Date]1, 2))), IF(OR(WEEKDAY([Due Date]1 + IF([Start Date]@row < TODAY(), ABS(CEILING(TODAY() - [Due Date]1, 2)))) = 1, WEEKDAY([Due Date]1 + IF([Start Date]@row < TODAY(), ABS(CEILING(TODAY() - [Due Date]1, 2)))) = 7), 1, 0)

Ipshita

Hi @Paul Newcome yes you are right. I had replied before seeing the comments of the poster… my bad! Thanks for adding in the correct formula here and for correcting me:)

Have a great day!

Ipshita

Dawn McCallister

@Paul Newcome @Ipshita - Thank you both for responding. I was out of the office on Friday, I will be giving this a try today and will get back to you as soon as possible.

Dawn McCallister

@Paul Newcome

I received a circular reference error ….

Paul Newcome

You wouldn't be able to put the formula in [Due Date]1 because you are referencing [Due Date]1 in the formula. You previously mentioned that would be the date entered to kick everything off.

Dawn McCallister

@Paul Newcome It's a Monday… It worked !!!! thank you

Paul Newcome

Happy to help. 👍️