# Current week & previous week

✭✭

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?

Tags:

• ✭✭✭✭✭✭

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.

Thanks

Thanks,

Kaveri Vipat

Senior Associate - Smartsheet Development, Ignatiuz Software

2023 Core Product Certified

• ✭✭✭✭✭✭

Hi @B Young,

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.

Thanks,

Kaveri Vipat

Senior Associate - Smartsheet Development, Ignatiuz Software

2023 Core Product Certified

• ✭✭✭✭✭✭

Hi @B Young,

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

• ✭✭✭✭✭✭

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.

Thanks

Thanks,

Kaveri Vipat

Senior Associate - Smartsheet Development, Ignatiuz Software

2023 Core Product Certified

• ✭✭

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

• ✭✭

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?

• ✭✭✭✭✭✭

Hi @B Young,

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.

Thanks,

Kaveri Vipat

Senior Associate - Smartsheet Development, Ignatiuz Software

2023 Core Product Certified

• ✭✭

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

• ✭✭

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!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!