Trying to place dates in monthly billing columns based on Award Start Date - Desperate for help!

ErinMay

Hello All,

I am in desperate need of assistance, this project is making me crazy. I admit to being more comfortable in Excel, and I always think I can do things in Smartsheet that I can do in Excel until I find out I can't. I would normally use VBA to do this, and am having trouble getting a cell formula to work. Currently throwing an #INCORRECT ARGUMENT SET error, as I'm sure I've totally messed up my awful, too-deep if statements.

All I need is this:

This sheet is for a single FY period July - June for each row. So, the only fields that should be filled out for this FY are the months that occurred after the Award Start Date month.

Based on the date in the [Award Start Date] column, place dates, in the appropriate Monthly Billing columns.

So if [Award Start Date] = 04/01/2018,

then only the [May Billing Due] = 05/01/2018

and then so on for [June Billing Due] = 06/01/2018

If [Award Start Date] = 06/01/2021 then I'd like to see all months filled out in the [[Month] Billing Due] columns (adding 30 days each time).

My attempt (just for if the [Award Start Date] was in April, mind you) at a nested IF, in lieu of a switch or case statement option: (It all goes wrong beginning at the DATE function).

There MUST be a simpler way to do this. Can anyone please assist. I'm going to be up all night until I can figure it out. :(

Jason Duryea

I'm not quite following the logic with the dates, but one thing I've noticed is the DATE() function in your formula is not following the proper format. It's supposed be DATE( year, month, day ), but your formula the functions are either missing YEAR and they're in the wrong order.

ErinMay

Hi, thanks for your reply. I did end up figuring it out, though the point you brought up is interesting and I'm going to have to check out where I landed. I'll update once I see.