Date based on a range that contains pm or am

Sharon Castiglia

Hello,

I currently have a formula for an end date that is the Install Date +1. This was put in place based on the assumption that a technician would start in the PM and finish the install in the AM (most are this way) or start in the AM and finish in the PM. The Departure time is also based on this assumption. My challenge is two fold:

If the tech is on site from 8:00 pm to 11:00 pm (the end date would be the same as the Install date) or 1:00 am to 6:00 am (this would be the Install date +1).

The formula below is based on the PM arrival/AM departure. I am not sure how to adjust this to accommodate the PM to PM and the AM to AM as well.

((VALUE(LEFT([Store Open Time]@row, FIND(":", [Store Open Time]@row) - 1)) + IF(CONTAINS("p", [Store Open Time]@row), IF(VALUE(LEFT([Store Open Time]@row, FIND(":", [Store Open Time]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Store Open Time]@row, FIND(":", [Store Open Time]@row) - 1)) = 12, -12))) + (VALUE(MID([Store Open Time]@row, FIND(":", [Store Open Time]@row) + 1, 2)) / 60)) + (([Install End Date]@row - [Install Date]@row) * 24)

Any assistance would be appreciated. Right now people are manually overriding the time calculation with 3.15 if the tech arrives at 8:00 pm and departs(store open) at 11:15 pm or 8:00 am to 11:15 am.

Thank you in advance

Sharon Castiglia

SoS | Dan Palenchar

Hi Sharon,

Can you provide a screenshot of your sheet?

Can you clarify the process by which this sheet is used, specifically which fields are entered manually and by whom, and which fields are intended to be calculated automatically?

Paul Newcome

Are you also tracking dates? If so, I may have a solution already built that would only require a small amount of tweaking to fit your needs.

I think THIS ONE may be adaptable. It does essentially the same thing it sounds like you are wanting to accomplish with the exception of where exactly the data is (am/pm in different cell vs same cell, etc), but those a relatively minor tweaks.

Sharon Castiglia

Hi Paul,

So everything has worked thus far other than the following:

I am getting an #unparseable on the following formula.

=INT([NCR Finish Time]1 - [NCR Start Time]1) + ":" + (([NCR FinishTime]1 - [NCR Start Time]1) - INT([NCR Finish Time]@1 - [NCR Start Time]@1)) * 60

Would it be possible to just convert the 6.55 (NCR Finish Time - NCR Start Time) to the hh.mm format?

Trying to get a formula for the Tech Arrival Hour (and once works the Store Open Hour which is the equivalent of the departure) and I am getting an incorrect argument set.

=IF(LEFT([Tech Arrival Time]1, 1 < 10, LEFT([Tech Arrival Time]1, 1, 1, LEFT([Tech Arrival Time]1, 2))))

Paul Newcome

Are you able to provide a screenshot of your sheet with sensitive/confidential data removed, blocked, an/or replaced with "dummy data" as needed?

Paul Newcome

Ah. I just saw the problem with the formula. Your last two cell references have a row of "@1". You need to either have "@row" OR "1".

Sharon Castiglia

No sensitive data. Also am to am and pm to pm are not calculating correctly. I can give you view access to this sheet if that would help.

Sharon Castiglia

Here is the formula that is still unparseable:

=INT([NCR Finish Time]1 - [NCR Start Time]1) + ":" + (([NCR FinishTime]1 - [NCR Start Time]1) - INT([NCR Finish Time]1 - [NCR Start Time]1)) * 60

Paul Newcome

Everything appears to be in order. Can you provide a screenshot that shows the formula in the sheet similar to the screenshot below? Depending on what is highlighted and what isn't it could give us more clues as to what is wrong.

Sharon Castiglia

I got this issue fixed. Used this formula

=VALUE(INT([Install Duration]@row) + "." + ([Install Duration]@row - INT([Install Duration]@row)) * 60)

The am to pm time is not calculating correctly. 8:00 am to 10:00 pm should be 14 hours but I am getting 38.

Because manual entry for the Tech Arrival hour and minute is not possible for our project, I need to have formulas in these fields. The tech arrival and departure minute formula works fine (=MID([Store Open Time]1, 2, 3). The Tech Arrival hour formula works fine for any hour that is less than 10

=VALUE(LEFT([Tech Arrival Time]1, 1)).

I don't know if there is a way to put an if statement that would change the position to 2 when the hour is 2 digits.

Any assistance with this one would be great.

Very grateful for your help

Sharon Castiglia

Paul Newcome

To accommodate the hour being either one or two digits, you can use...

=VALUE(LEFT([Tech Arrival Time]1, FIND(":", [Tech Arrival Time]1) - 1))

Sharon Castiglia

That worked great for the hour, but created a problem with the Tech Arrival Minutes (goes from :00 to 0:0)

Original formula: =MID([Tech Arrival Time]4, 2, 3)

Changed it to: =IF([Tech Arrival Hour]5 > 9, MID([Tech Arrival Time]5, 3, 3))

which worked if the value of the Tech Arrival Hours are greater than 9, so tried to incorporate the <10 and got the following:

=IF([Tech Arrival Hour]5 > 9, VALUE(MID([Tech Arrival Time]5, 3, 3)), IF([Tech Arrival Hour]5 < 10, VALUE(MID([Tech Arrival Time]5, 2, 3)), 0))

But am getting #INVALID VALUE function error

Thoughts?

Paul Newcome

That is because the MID function is starting in position 3 when you have a two digit hour which is the : which is throwing off your VALUE function. Try starting that one in position 4.

My suggestion for the minutes would be...

=VALUE(MID([Tech Arrival Time]5, FIND(":", [Tech Arrival Time]5) + 1, 2))

Paul Newcome

I think your sheet may be overly complicated. I made a copy of your sheet's column names and did up a different solution that should give you what you need. The formulas for each column are listed below the screenshot.

[Total Onsite Time (hh:mm)]:

=INT([Install Duration]@row) + ":" + IF((([Install Duration]@row - INT([Install Duration]@row)) * 60) < 10, "0") + (([Install Duration]@row - INT([Install Duration]@row)) * 60)

[Install Duration]:

=Open@row - Arrival@row

Arrival:

=VALUE(LEFT([Tech Arrival Time]@row, FIND(":", [Tech Arrival Time]@row) - 1)) + IF(CONTAINS("p", [Tech Arrival Time]@row), IF(VALUE(LEFT([Tech Arrival Time]@row, FIND(":", [Tech Arrival Time]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Tech Arrival Time]@row, FIND(":", [Tech Arrival Time]@row) - 1)) = 12, -12)) + (VALUE(MID([Tech Arrival Time]@row, FIND(":", [Tech Arrival Time]@row) + 1, 2)) / 60)

Open:

=VALUE(LEFT([Store Open Time]@row, FIND(":", [Store Open Time]@row) - 1)) + IF(CONTAINS("p", [Store Open Time]@row), IF(VALUE(LEFT([Store Open Time]@row, FIND(":", [Store Open Time]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Store Open Time]@row, FIND(":", [Store Open Time]@row) - 1)) = 12, -12)) + (([Install End Date]@row - [Install Date]@row) * 24) + (VALUE(MID([Store Open Time]@row, FIND(":", [Store Open Time]@row) + 1, 2)) / 60)

Sharon Castiglia

OK got that fixed for the minutes by changing my position to 4. I figured out why I was getting incorrect error calculation. Problem with Install End Date calculation (which I am now getting an #imparseable (snip with formula included):

In case the screen print is too hard to read:

=IF(AND(([Tech Arrived AM/PM]@row = "A", [Store Open AM/PM]@row = "P", [Install Date]@row, IF([Tech Arrived AM/PM]@row = [Store Open AM/PM]@row, [Install Date]@row, [Install Date]@row + 1)

I think this is the last piece of my puzzle (man I hope so).

Paul Newcome

You have an extra opening parenthesis after the AND.