Handling request times within certain business hours

christopher.jordan

Hi

I am looking for a way to calculate the number of days between when a row is added and when it is marked as "complete."

I currently have a column set up with the submission timestamp. I also have a "date completed" column. I want to exclude weekends and holidays which seems simple enough, but I would also like to set a criteria that if a row is added past a particular time (6pm), that day is not counted towards the turnaround time.

I was thinking of trying to figure out a formula that could maybe be added to another column where if the time on the original submission was past 6pm, it would return the next date, and then do the networkdays formula between that date and the completed date. I haven't been able to come up with such a formula that would do this however. I am also open to completely different solution if there are better ways to do this.

Thanks a lot.

Paul Newcome

This will give you the hour that it was created:

 

=VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - FIND(" ", Created@row) - 1))

 

You can use that in an IF/AND statement with this

 

=FIND("P", Created@row)

 

to flag if it is submitted after 6PM for the given date. You can either use it to check a box or write it directly into the formula you are using to calculate the number of days.

 

To check a box:

=IF(AND(VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - FIND(" ", Created@row) - 1)) > 6, FIND("P", Created@row) > 0), 1)

.

You could then us an IF statement to calculate the days based on the box being checked or not.

 

=IF([Checkbox Column]@row = 1, Your Original Formula - 1, Your Original Formula)

.

There are a few ways to write it if you are skipping the checkbox. The two most straight forward would be:

1: Use the IF statement within the NETWORKDAYS function to adjust your start date...

=NETWORKDAYS(IF(AND(VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - FIND(" ", Created@row) - 1)) > 6, FIND("P", Created@row) > 0), DATEONLY(Created@row) + 1, DATEONLY(Created@row)), [Completion Date]@row)

.

2: Run an "either/or" NETWORKDAYS function based on the result of the IF statement...

=IF(AND(VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - FIND(" ", Created@row) - 1)) > 6, FIND("P", Created@row) > 0), NETWORKDAYS(Created@row, [Completion Date]@row) - 1, NETWORKDAYS(Created@row, [Completion Date]@row))