I need help! I need to calculate a date based on hire type and start date

Danielle Baker

If Hire Type is New Hire, and OD Start Date is a valid date, Then NEW HIRE Request for Contract Date needs to be 2 weeks (14 days ahead of start date)

Can anyone please help me? The formula i have typed, I screenshotted. and I am getting a 'circular' error.

Ray Lindstrom

You're getting a circular error because you're trying to use the value from your "Hire Type" column to calculate a value for your "Hire Type" column. That formula will always be chasing it's tail.

Your second IF criteria states that IF [OD Start Date]@row = "True". The problem with this is that [OD Start Date] column is not a True/False column such as a checkbox or flag. It's a date column, so it will never be True or False.

Your final IF criteria states "[NEW Hire Request for Contract Date]@row = Date" would also cause an error. First it's surrounded by "" which means everything in-between should be treated as a string of text. It's not a criteria statement that will be evaluated. Second there's no "= Date" functionality that will tell you the value is a valid date. If you have that column set as a Date column that is restricted to dates only, then any entry will be a valid Date otherwise the data wouldn't have been accepted into the cell.

Try this in your [NEW Hire Request for Contract Date] column: =IF(AND(NOT(ISBLANK([OD Start Date]@row)), [Hire Type]@row = "New Hire"), [OD Start Date]@row - 14, "")

The [NEW Hire Request for Contract Date] column must be a Date type column for this to work.

Ray Lindstrom

You're getting a circular error because you're trying to use the value from your "Hire Type" column to calculate a value for your "Hire Type" column. That formula will always be chasing it's tail.

Your second IF criteria states that IF [OD Start Date]@row = "True". The problem with this is that [OD Start Date] column is not a True/False column such as a checkbox or flag. It's a date column, so it will never be True or False.

Your final IF criteria states "[NEW Hire Request for Contract Date]@row = Date" would also cause an error. First it's surrounded by "" which means everything in-between should be treated as a string of text. It's not a criteria statement that will be evaluated. Second there's no "= Date" functionality that will tell you the value is a valid date. If you have that column set as a Date column that is restricted to dates only, then any entry will be a valid Date otherwise the data wouldn't have been accepted into the cell.

Try this in your [NEW Hire Request for Contract Date] column: =IF(AND(NOT(ISBLANK([OD Start Date]@row)), [Hire Type]@row = "New Hire"), [OD Start Date]@row - 14, "")

The [NEW Hire Request for Contract Date] column must be a Date type column for this to work.

Ray Lindstrom

Here it is at work:

The NEW Hire Request for Contract Date for the 2nd row is not populated because the Hire Type is not New Hire.

The NEW Hire Request for Contract Date for the 3rd row is not populated because there is no OD Start Date.

Danielle Baker

You are a life saver!!!

SmartSheetUser01

Can we have dates auto-populate in the automation workflow? Not to record the current dates but exactly this approach where we would like to add 7 or 30 days on the start date. It seems like the date column doesn't show during the automation workflow to edit for formula based field.

Ray Lindstrom

@Danielle Baker, I'm so glad it worked for you!

@SmartSheetUser01, Unfortunately you cannot do calculations like that through automation. I wish you could, and I wish you could apply formulas via automation. Maybe someday!

SmartSheetUser01

thanks appreciate it!