Hello There,

Im trying to write a new sheet to log hours for calls so that I can claim all hours worked whilst on call back.

I have a cell with a time format in that i use to enter the time when the call is logged, i then have another cell in time format to log when the call was finished, a third cell calculates the time of the call (all pretty straight forward so far).

basically, I have time restrictions on the hours that I am allowed to claim back. This being for example, not being allowed to claim back any hours worked before 10pm.

I am trying to write an if statement that will first look at the time in Cell 1 and check that it is before 10pm, the result being automatically 0 as this is before claimable time. If after 10pm, to just display the amount of time owed. Im struggling with what to put in as my actual criteria

I had tried If(Cell1>10:00, Cell3, 0) this was just a basic start which i realised failed as in excel 10:00 is acutally specifying a range, when i tried with just the number 10 in, it brought me back the answer as 0.

Is it possible to work with Time regarding if statements or is there a better way I could go about this.

Thanks for Reading.

Hi,

Best to use 24 hour format for the times so, for example, Excel can distinguish between 10am and 10pm. A start time of 9.55pm could be input as 21:55.

So I understand from your post that if a call started at 21:55 and went on to 22:10, the call lasted for 15 minutes but you would only claim for the 10 minutes after 22:00.

Suppose column A has the start times and column B has the end times, and assuming that your calls always finish before midnight, you could use this formula starting in row 2:

=IF(B2>TIME(22,0,0),B2-MAX(TIME(22,0,0),A2),0)

The cell containing this formula should be formatted to show a time result, for example [mm] to show the result (10) in minutes.

This formula says "If the call finished after 10pm then work out how many minutes after 10pm I was on the call for, otherwise return 0".

If you prefer, you could use 22/24 instead of TIME(22,0,0).

Hope that helps...

Thank you very much Colin, that does pretty much what I am after, with a little tweaking I will be able to get it to fit my needs perfectly (there is nothing wrong with your statement, I just have a few more contraints thats all).

Once again thanks.

On a side not, i know the solution you gave me works for calls between 10pm and Midnight, basically, my on call rota goes from 5:30pm-8:00am the following morning. Of these hours I am only able to claim back time from 10:00pm-8:00am

I am now thinking that the calculations I was after are not possible, but if there is a solution to that problem, I would highly appreciate it.

Hi,

If times carry on after midnight then you need to include both the date and the time in each cell. Instead of 22:00, use 12/12/2009 22:00 etc...

The formula we were using will also need to be modified slightly and then Excel will then automatically be able to cater for date overlap within the calculations.
If you let me know the other constraints (if there are any other than those mentioned in post #4) then I might be able to suggest something specific?

Hope that helps...