Days since

Izzyz88

New user to Smartsheets and the logic application from Excel seem to be getting me in trouble. 

Looking for an equation (Days Waiting on funding) that will check four columns (Estimate submitted 1,2,3,4). If a date is present how many work days (less holidays) have passed. I also want the "days awaiting on funding" to be "received" if a date is present in columns (PO received 1,2,3,4).

I have my holiday dates saved in a second sheet that is not shared. My perception is referencing a second sheet that my view only team members do not have may cause an error at a later time as well. 

 

Thank you for any help

 

eric.o

Hello,

From my understanding, you're trying to create a formula where if a Date column contains a date, determine how many days have passed. However, you only want the formula to trigger if all 4 columns contain a date. In addition, if the columns do contain a date, produce cumulative days passed.

This can be achieved utilizing a formula similar to this:

 

=IF(AND(ISDATE([Column 1]@row), ISDATE([Column 2]@row), ISDATE([Column 3]@row), ISDATE([Column 4]@row)), (TODAY()-[Column 1]@row) + (TODAY()-[Column 2]@row) + (TODAY()-[Column 3]@row) + (TODAY()-[Column 4]@row))

 

If you do not want the Dates to be cumulative, the single IF Function for a single Date column reference could look like this:

=IF(ISDATE([Column 1]@row), Today()-[Column 1]@row)

 

If you desire 'PO Received' for columns 1,2,3,4 to appear as 'Received', when a date is in the 'Days Awaiting on Funding' column 1,2,3,4, you could achieve this utilizing a formula similar to this.

=IF(ISDATE([Column 1]@row), "Received")

Note: The above formula would be placed in the desired 'PO Received' cell and alter accordingly to the reference column. 

 

Please let us know if you have any questions on the above.

Cheers, 

Eric  

Smartsheet Support