IF formula to modify Month/Year of date column
Hi - I'm capturing a timestamp for when data is submitted into my form [Date Submitted]. I'm attempting to create a formula that converts that timestamp into a mm/yyyy format while subtracting 1-month from the timestamp. As an example, with a timestamp = 07/29/2022 my output should be 06/2022. If timestamp = 11/12/2022 my output should be 10/2022.
I'm able to get the formula to work for months with single digits (1-9) but I can't figure out how to account for (10-12). Here is what does work:
=IFERROR(IF(LEN(MONTH([Date Submitted]@row)) = 1, "0" + (MONTH([Date Submitted]@row) - 1), MONTH([Date Submitted]@row)) + "/" + (YEAR([Date Submitted]@row)), "")
How do I modify this IF statement to account for the 2-digits months in my original timestamp?
Answers
-
Try this...
=IF(MONTH([Date Submitted]@row = 1, 12, IF(MONTH([Date Submitted]@row) - 1< 10, "0") + "" + MONTH([Date Submitted]@row) - 1) + "/" + YEAR([Date Submitted]@row)
Help Article Resources
Categories
Check out the Formula Handbook template!