Backdate If Function

Alex (Giant Pics Accts)

Hello,

I'm trying to construct a formula that backdates a certain amount of days depending on the date in a separate column and another criteria. See example below.

If Audi or Infiniti is chosen, it would back date 30 days and Lexus or VW 45 days from the Sell Date. I have the formula to backdate below but can't seem to incorporate the car makers criteria.

Output Date Column = May 1, 2021

Sell Date = Jun 15, 2021

Car Makers = Audi, Infiniti, Lexus, VW

=[Date Column]@row - 42

Mike Wilday

Does this do the job?

=IF(OR([Car Makers]@row = "Audi", [Car Makers]@row = "Infiniti"), [Date Column]@row - 30, IF(OR([Car Makers]@row = "Lexua", [Car Makers]@row = "VW"), [Date Column]@row - 45))

Make sure the column headers match your actual column headers.

Heather Duff

Hi Alex,

Try this:

=if(OR([car maker]@row="Audi",[car maker]@row="Infiniti"),[sell date] - 30,if(OR([car maker]@row="Lexus",[car maker]@row="VW"),[sell date] - 45,""))

Let me know if it works!

Best,

Heather

Alex (Giant Pics Accts)

Thanks but still have a couple of issues. It seems when I modify the formula to include either 1 item or more than 2 items to a specific backdate, it doesn't seem to work. How do I adjust the formula to account for it. Also, is there way for the format date to be only the Month & Year, e.g. Jul 2021.

=IF(OR(Platform@row = "iTunes (Apple TV)", [LIve Date]@row - 30, IF(OR(Platform@row = "Amazon (PVD) Prime Video Direct", Platform@row = "FandangoNow"), Platform@row = "Vudu (TVOD/AVOD)", [LIve Date]@row - 60))

*I used the actual column names to keep it simple for me to copy over.

Mike Wilday

Hi Alex, I think you're close, but I think logic isn't coming across correctly. Does this work the way you want it to?

=IF(Platform@row = "iTunes (Apple TV)", [LIve Date]@row - 30, IF(OR(Platform@row = "Amazon (PVD) Prime Video Direct", Platform@row = "FandangoNow", Platform@row = "Vudu (TVOD/AVOD)"), [LIve Date]@row - 60))

Should Fandango, Prime, and Vudo all be -60?

Alex (Giant Pics Accts)

@Mike Wilday

I've decided to use another category because the formula was getting quite large. Basically, I require the date to go back 30 days, 60 days, 90 days & forward 30 days and 90 days from the Live Date. I've created another column where I've assign each platform an Avail Timing of -30, -60, -90, +30 & +90.

=IF([Avail Timing]@row = "-30", [Live Date]@row - 30), IF([Avail Timing]@row = "-60", [Live Date]@row - 60), IF([Avail Timing]@row = "-90", [Live Date]@row - 90), IF([Avail Timing]@row = "+30", [Live Date]@row + 30), IF([Avail Timing]@row = "+90", [Live Date]@row + 90)

Also, is there a way to format the date into a Month and Year such as Mar 2021?

Mike Wilday

Yes, but that will also increase the formula length... :D

=IF([Avail Timing]@row = "-30", Month([Live Date]@row - 30)+" "+Year([Live Date]@row - 30), IF([Avail Timing]@row = "-60", Month([Live Date]@row - 60)+" "+Year([Live Date]@row - 60), IF([Avail Timing]@row = "-90", Month([Live Date]@row - 90)+" "+Year([Live Date]@row - 90), IF([Avail Timing]@row = "+30", Month([Live Date]@row + 30)+" "+Year([Live Date]@row + 30), IF([Avail Timing]@row = "+90", Month([Live Date]@row + 90)+" "+Year([Live Date]@row + 90))))))

Alex (Giant Pics Accts)

@Mike Wilday

It's not working for the +30 or +90 for some reason. I'm not getting an error message and it simply just blank.

Also, the date format is all numeric (5 2021) but looking for (May 2021). Is it possible to adjust in the formula?

Thanks!

Alex (Giant Pics Accts)

One more thing, when I remove the pluses and list as 30 or 90, the formula works.

Mike Wilday

Can you share the formula as you have it working?

Alex (Giant Pics Accts)

Here is what works.

=IF([Avail Timing]@row = "-30", MONTH([LIve Date]@row - 30) + " / " + YEAR([LIve Date]@row - 30), IF([Avail Timing]@row = "-60", MONTH([LIve Date]@row - 60) + " / " + YEAR([LIve Date]@row - 60), IF([Avail Timing]@row = "-90", MONTH([LIve Date]@row - 90) + " / " + YEAR([LIve Date]@row - 90), IF([Avail Timing]@row = "30", MONTH([LIve Date]@row + 30) + " / " + YEAR([LIve Date]@row + 30), IF([Avail Timing]@row = "90", MONTH([LIve Date]@row + 90) + " / " + YEAR([LIve Date]@row + 90))))))

Mike Wilday

Okay. I would do this by creating a couple columns in your sheet... One column should be called Month_Number thern enter the numbers 1-12 and another should be called Month_Name and align the month number with the corresponding name. Then try this formula:

=IF([Avail Timing]@row = "-30", Vlookup(MONTH([LIve Date]@row - 30), Month_Number:Month_Name, 2, false) + " / " + YEAR([LIve Date]@row - 30), IF([Avail Timing]@row = "-60", Vlookup(MONTH([LIve Date]@row - 60),Month_Number:Month_Name, 2, false) + " / " + YEAR([LIve Date]@row - 60), IF([Avail Timing]@row = "-90", vlookup(MONTH([LIve Date]@row - 90),Month_Number:Month_Name, 2, false), + " / " + YEAR([LIve Date]@row - 90), IF([Avail Timing]@row = "30", vlookup(MONTH([LIve Date]@row + 30),Month_Number:Month_Name, 2, false) + " / " + YEAR([LIve Date]@row + 30), IF([Avail Timing]@row = "90", vlookup(MONTH([LIve Date]@row + 90), Month_Number:Month_Name, 2, false) + " / " + YEAR([LIve Date]@row + 90))))))

Alex (Giant Pics Accts)

Thanks! I was able to get it to work. One more thing I need assistance on is with the formula below.

Basically, anything with Amazon or iTunes should output the "Ordered_Output Platform" otherwise it will be "Need to Avail."

=IF(OR(Platform@row = "Amazon PVD Prime Video Direct", Platform@row = "iTunes (Apple TV)", "Ordered_Output Platform", "Need to Avail")

Mike Wilday

Hi Alex, Sorry i was on vacation last week.

You just need to close the OR statement before you finish the IF statement. Placing a closing parenthesis after the Apple TV bit should do it.

=IF(OR(Platform@row = "Amazon PVD Prime Video Direct", Platform@row = "iTunes (Apple TV)"), "Ordered_Output Platform", "Need to Avail")