Current week & previous week

B Young

I have a sheet with start date and finish date columns and a column to insert current/previous or blank (if the dates are neither current or previous weeks), so was thinking the formula would be something like if today's date is greater than or equal to the start date or less than or equal to the finish date insert "Current", but if its from the previous week insert "Previous", if it is neither current nor previous leave it blank.

Where do I go with this one?

Kaveri Vipat

Hi @B Young,

I hope you are doing well,

You can use this formula to calculate the current, and previous week on the bases of the start and finish date

=IF(AND(TODAY() >= [start date]@row, TODAY() <= [finish date]@row), "Current", IF(AND(TODAY() - [finish date]@row <= 7, TODAY() - [finish date]@row > 0), "Previous", ""))

 

For Previous – The previous condition is based on the finished date, according to the conditions we can’t get the previous based on the start date.

If you have another condition, feel free to ask me.

Hope this helps, if you have any questions please ask! 😊

Thanks 

Kaveri Vipat

Hi @B Young,

It's completely fine, we are happy to help you

According to your new condition, you can try this formula

=IF(AND(TODAY() >= [start date]@row, TODAY() <= [finish date]@row), "Current", IF(AND(TODAY() - [finish date]@row <= 7, TODAY() - [finish date]@row > 0), "Previous", IF(AND(TODAY() - [start date]@row >= -7, TODAY() - [start date]@row <= -1), "Next", "")))

Next week's conditions are based on the start date, if the start date is within the next 7 days, you will get “Next”, if the start date is more than 7 days so it will be blank.

Nick Korna

Hi @B Young,

Are your weeks starting on Mondays or are they another day range (Sunday-Saturday)?

Kaveri Vipat

Hi @B Young,

I hope you are doing well,

You can use this formula to calculate the current, and previous week on the bases of the start and finish date

=IF(AND(TODAY() >= [start date]@row, TODAY() <= [finish date]@row), "Current", IF(AND(TODAY() - [finish date]@row <= 7, TODAY() - [finish date]@row > 0), "Previous", ""))

 

For Previous – The previous condition is based on the finished date, according to the conditions we can’t get the previous based on the start date.

If you have another condition, feel free to ask me.

Hope this helps, if you have any questions please ask! 😊

Thanks 

B Young

Thank you this formula works! Our start date is a Monday.

B Young

Sorry I have an additional condition to add - what about next week? Could we include "Next" if the start and finish date is next week?

Kaveri Vipat

Hi @B Young,

It's completely fine, we are happy to help you

According to your new condition, you can try this formula

=IF(AND(TODAY() >= [start date]@row, TODAY() <= [finish date]@row), "Current", IF(AND(TODAY() - [finish date]@row <= 7, TODAY() - [finish date]@row > 0), "Previous", IF(AND(TODAY() - [start date]@row >= -7, TODAY() - [start date]@row <= -1), "Next", "")))

Next week's conditions are based on the start date, if the start date is within the next 7 days, you will get “Next”, if the start date is more than 7 days so it will be blank.

B Young

Thank you - this is excellent - really appreciate you help!

B Young

Can I bother you to add another condition? If today's date isn't in the current, previous or next week and not in the future, could it say "Past"? I've had a few attempts at adjusting the above formula but been unsuccessful!

Thank you!