End Date Calculation

Options
✭✭✭✭✭

Hello,

I currently have a project that typically runs overnight (pm to am) and time calculations are based on an end date that is the install date + 1 day. I need to figure out a calculation for when the installation is not overnight (i.e. 8:00 pm to 11:30 pm) Below is the current formula.

=IF([Tech Name]@row > "", IF([Tech Arrival Time-Calculation]@row > 1, [Install Date]@row + 1, [Install Date]@row), "")

I have tried the following:

=IF(FIND("pm", [Tech Departure Time]@row), [Install Date]@row, [Install Date]@row + 1)

Getting an invalid Data Type

Both columns (install date and install end date) are date columns that are not restricted to dates only.

I am stumped.

Sharon Castiglia

• ✭✭✭✭✭✭
Options

I think you would need this to be a Date column. IF you have a reason to keep it a Text/Number column then add +"" to the end

• ✭✭✭✭✭
Options

Hello Ross,

Thank you for the quick response time. I appreciate you taking time out of your day to help me.

The install time entry is a text/data column. I tried changing it to a date column allowing non date entries and it turns into a date. I tried adding the +"" after the [Install Date]@row and it still an invalid data type. When you say the "end" what end are you speaking to?

Thank you

• ✭✭✭✭✭✭
edited 06/18/20
Options

adding the +"" to the end would convert the date 06/18/20 to a text string and allow it to be shown in a Text/Number Column type. when i say at the end it would be the end of your formula example would be =IF(FIND("pm", [Tech Departure Time]@row), [Install Date]@row, [Install Date]@row + 1)+""

• ✭✭✭✭✭
Options

Hi Ross,

Are you talking about the cell with the tech departure of 9:00 PM (manually entered)?

I apologize for my confusion.

Sharon

• ✭✭✭✭✭✭
Options

it sounds like you are trying to get a date value to report into at text column is that correct? you want the install date to show up unless it happens at night then you want the install date +1 day so it would be the next day. if you use a date column your original formula would work because it is reporting out a date.

formula for a date column

=IF(FIND("pm", [Tech Departure Time]@row), [Install Date]@row, [Install Date]@row + 1)

formula for a Text/Number column

=IF(FIND("pm", [Tech Departure Time]@row), [Install Date]@row, [Install Date]@row + 1)+""

• ✭✭✭✭✭
Options

I changed the Install End Date to a Text/Number column using the +"" in the formula you showed above and I am still get the #Invalid Data Type. The install Date column is a Date column.

I am at a loss

• ✭✭✭✭✭✭
Options

would you mind sharing a screen shot of the sheet?

• ✭✭✭✭✭
Options

Paul, Thank you so much. As always, the community to the rescue.

• ✭✭✭✭✭✭
Options

Happy to help! 👍️