End Date Calculation
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
Best Answer
-
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)
Answers
-
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
-
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
-
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)+""
-
Hi Ross,
Are you talking about the cell with the tech departure of 9:00 PM (manually entered)?
I apologize for my confusion.
Sharon
-
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)+""
-
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
-
would you mind sharing a screen shot of the sheet?
-
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)
-
Paul, Thank you so much. As always, the community to the rescue.
-
