working hours formula

[Chigils]

Good afternoon. I have a .csv file that I have downloaded from my company ticketing system. The way the data is formatted is of little use and I need to manipulate it to retrieve any information from the data. The report records at what time of the day tickets are created. What I'd like to do is report on whether the tickets are raised within my company business hours or outside of business hours. Does anyone have any suggestions how I can do this? For information, my business hours are 09:00-17:15 Mon-Friday, and tickets are raised at all hours of day and night every day

 

[NeonRedSharpie]

What does the data look like when it comes in? Is it in long date format? Is it just a number?

 

[Chigils]

Good question - it comes in looking like this hh:mm:ss (24hr)

 

[NeonRedSharpie]

Fun fact about time in excel. It's just a numeric based on 24 hours = 1. So, 9am is 0.375 and 17:15 is 0.71875. Thus, your formula is just:

=IF(AND(A1>=0.375,A1<=0.71875),"Business Hours","Not Business Hours")

Where A1 is your time.

 

[Chigils]

Thank you, very useful - and it works