Current & Previous Month formula

B Young

I'm trying to work out a formula that says if the date in the [Date Column] is the current month insert the word current, but if it's in the previous month insert previous, if it isn't either of those leave blank. What's the best way to do this?

Shubham

Hi B Young 

I hope you are doing well, 

As per your requirements you can use this formula. 

=IF(AND(YEAR(TODAY()) = YEAR([Date Column]@row), MONTH(TODAY()) = MONTH([Date Column]@row)), "current", IF(AND(YEAR(TODAY()) - 1 = YEAR([Date Column]@row), MONTH(TODAY()) = 1, MONTH([Date Column]@row) = 12), "Previous", IF(AND(YEAR(TODAY()) = YEAR([Date Column]@row), MONTH(TODAY()) = MONTH([Date Column]@row + 1)), "Previous", ""))) 

 

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

Thanks  

Shubham Umale,

Smartsheet Engineer, Ignatiuz Software 

Genevieve P.

Aha! Thanks for outlining this further. I found where the problem was with the original formula, one of the closing parentheses is in the wrong place.

When looking for the previous month, we want the add the +1 after we've found the MONTH, like so:

MONTH(TODAY()) = MONTH([Date Column]@row) + 1)

Try the original formula with the correct parentheses:

=IF([Date Column]@row = "", "", IF(AND(YEAR(TODAY()) = YEAR([Date Column]@row), MONTH(TODAY()) = MONTH([Date Column]@row)), "Current", IF(AND(YEAR(TODAY()) - 1 = YEAR([Date Column]@row), MONTH(TODAY()) = 1, MONTH([Date Column]@row) = 12), "Previous", IF(AND(YEAR(TODAY()) = YEAR([Date Column]@row), MONTH(TODAY()) = MONTH([Date Column]@row) + 1), "Previous", ""))))

Cheers,

Genevieve

Shubham

Hi B Young 

I hope you are doing well, 

As per your requirements you can use this formula. 

=IF(AND(YEAR(TODAY()) = YEAR([Date Column]@row), MONTH(TODAY()) = MONTH([Date Column]@row)), "current", IF(AND(YEAR(TODAY()) - 1 = YEAR([Date Column]@row), MONTH(TODAY()) = 1, MONTH([Date Column]@row) = 12), "Previous", IF(AND(YEAR(TODAY()) = YEAR([Date Column]@row), MONTH(TODAY()) = MONTH([Date Column]@row + 1)), "Previous", ""))) 

 

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

Thanks  

Shubham Umale,

Smartsheet Engineer, Ignatiuz Software 

B Young

Thank you - This works perfectly. Appreciate your help

B Young

Hello again, I've discovered an issue with this formula. It's inserting the current month correctly, but not the previous month, it's leaving it blank. It's also leaving any previous months blank, but if there is no date it's saying #INVALID DATA TYPE. This is a copy and paste of the formula - just in case there is something I can't see that is different to the suggested formula. I've check the date column and it's a date column type. And the column with the formula in it is a text/number column type.

=IF(AND(YEAR(TODAY()) = YEAR([Date Column]@row), MONTH(TODAY()) = MONTH([Date Column]@row)), "Current", IF(AND(YEAR(TODAY()) - 1 = YEAR([Date Column]@row), MONTH(TODAY()) = 1, MONTH([Date Column]@row) = 12), "Previous", IF(AND(YEAR(TODAY()) = YEAR([Date Column]@row), MONTH(TODAY()) = MONTH([Date Column]@row + 1)), "Previous", "")))

Can you help?

Genevieve P.

Hiya! 🙂

To get rid of the error when there's a blank date, you can add another quick IF statement at the front:

=IF([Date Column]@row = "", "",

So:

=IF([Date Column]@row = "", "", IF(AND(YEAR(TODAY()) = YEAR([Date Column]@row), MONTH(TODAY()) = MONTH([Date Column]@row)), "Current", IF(AND(YEAR(TODAY()) - 1 = YEAR([Date Column]@row), MONTH(TODAY()) = 1, MONTH([Date Column]@row) = 12), "Previous", IF(AND(YEAR(TODAY()) = YEAR([Date Column]@row), MONTH(TODAY()) = MONTH([Date Column]@row + 1)), "Previous", ""))))

For the blank cells for previous months, would you be able to post a screen capture, identifying the dates you're looking at (but blocking out sensitive data)?

This formula should return "Previous" if the month listed is the directly previous month... so if the date is in January, and we're in February. However it will be blank if it's any other previous month... for example if the date is December or November and we're in February. Does that make sense?

Cheers,

Genevieve

B Young

Thank you for your reply. And adding the IF statement at the start resolves the blank issue. I have attached a sheet which shows you the data. You can see the [Month] column is the one with the formula in it and it's a column formula. The [Date Column] has the dates in it. The current month dates are working fine, and row 2 the previous month date is working ok, but any other rows with the date in Jan 23 it isn't inserting 'Previous' in the month column.

Current & previous months.png

You are correct I was hoping for the fomula to say Current for the current month (so right now any dates Feb 23 would be "Current") and Previous for any direct previous month (so right now any dates in Jan 23 would be "Previous") and any date outside of this would be blank.

Formula.png

This is an image of the formula

Genevieve P.

Thanks for clarifying, @B Young!

The current formula is only looking for the directly previous month. We can definitely adjust this though!

=IF([Date Column]@row = "", "", IF(AND(YEAR(TODAY()) = YEAR([Date Column]@row), MONTH(TODAY()) = MONTH([Date Column]@row)), "Current", IF([Date Column]@row < TODAY(), "Previous")))

This first checks to see if the Month and Year are today's date, and if it is, say "Current". Otherwise, if the Date is in the past compared to Today (so any date in the past at all, excluding today's month), then it should return "Previous".

Let us know if this works for you!

Cheers,

Genevieve

B Young

Thank you - I'm sorry I don't think I have explained this correctly. I was hoping for previous to only be inserted in the last month not all the previous months. So we are in Feb 23 now, any dates with Feb 23 will be current and Jan 23 dates will be previous, but if the date is blank or not in Feb 23 or Jan 23 it would be blank. Does this make sense? The formula you have provided above is inserting previous in any dates are are before Feb 23.

Current & Previous with new formula.png

Genevieve P.

Aha! Thanks for outlining this further. I found where the problem was with the original formula, one of the closing parentheses is in the wrong place.

When looking for the previous month, we want the add the +1 after we've found the MONTH, like so:

MONTH(TODAY()) = MONTH([Date Column]@row) + 1)

Try the original formula with the correct parentheses:

=IF([Date Column]@row = "", "", IF(AND(YEAR(TODAY()) = YEAR([Date Column]@row), MONTH(TODAY()) = MONTH([Date Column]@row)), "Current", IF(AND(YEAR(TODAY()) - 1 = YEAR([Date Column]@row), MONTH(TODAY()) = 1, MONTH([Date Column]@row) = 12), "Previous", IF(AND(YEAR(TODAY()) = YEAR([Date Column]@row), MONTH(TODAY()) = MONTH([Date Column]@row) + 1), "Previous", ""))))

Cheers,

Genevieve

B Young

Thank you - this has solved the problem. Sorry for the confusion! Appreciate your help.

Genevieve P.

Glad to hear it worked for you! 🙂