End Date Calculation

Sharon Castiglia

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.

Thank you in advance

Sharon Castiglia

Paul Newcome

The FIND function generates a numerical value based on where within the searched text your specified string is located.

So using the FIND function to evaluate a cell containing 11:30pm and searching for "pm", the FIND will output 6.

Based on this, you would need to adjust your IF statement to say that if the FIND is greater than 0 meaning "pm" was found somewhere within the evaluated cell.

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

RossL

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

Sharon Castiglia

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

RossL

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)+""

Sharon Castiglia

Hi Ross,

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

I apologize for my confusion.

Sharon

RossL

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)+""

Sharon Castiglia

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

RossL

would you mind sharing a screen shot of the sheet?

Paul Newcome

The FIND function generates a numerical value based on where within the searched text your specified string is located.

So using the FIND function to evaluate a cell containing 11:30pm and searching for "pm", the FIND will output 6.

Based on this, you would need to adjust your IF statement to say that if the FIND is greater than 0 meaning "pm" was found somewhere within the evaluated cell.

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

Sharon Castiglia

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

Paul Newcome

Happy to help! 👍️