Time Calculation giving value error only for certain times

[August]

Hi
I'm using the formula below as part of a labour time sheet calculator

This particular formula gives me additional wrap overtime above and beyond the working day
C48 is the contracted hours in a day
E48 is an allowance that reduces wrap overtime
F48 is the start time
G48 is the finish time
K48 is an overtime payment that can reduced the wrap overtime if paid
Cells are formatted as 00\:00_-
Formula cell is formatted as hh:mm_-

=CEILING(TEXT(G48,"00\:00")-TEXT(F48+C48+E48,"00\:00")-K48/24,0.5/24),0)

The problem I'm having is that for certain values of F48 i get a #value error
(eg if the value is 12.30 no problem but if the value is 14.30 then i get the value error)

I should add that this is only happening because of night work when the start and finish times span midnight

I hope that all makes sense and that someone can help

 

[jasonb75]

I'm still (unsuccessfully) trying to make your formula work.

As I understand it, all of the cells in B48:I48 are formatted as 00\:00, which I've done. I've entered the same times as your post above, but your adj formula is giving me 12:30, not 01:00??

Additionally, I think that the part, C48+D48+E48 is going to be problematic because you're adding incorrect factors. As a simple example.

0945+0045+0045 = 1035
09:45+00:45+00:45 = 11:15

If you can be 100% certain that the minutes (excluding hours) of the 3 cells will never total to >99 (i.e. if none should ever have 45 as the last 2 digits) then you should theoretically be safe, but I haven't tested this theory.

 

[August]

B48:G48 are formatted 00\:00_-, as they are data input cells
H48:I48 are formatted hh:mm_-, as they are the calculations of Total hours and Pre Call overtime respectively

I was just lucky with the incorrect factors as C48 can only every be 1000,1030 or 1200
and D48 can only ever be 0100 or 0000, E48 0030 or 0000
Although when the values are 1000, 0100 and 0030 the values are correct and over 99

Looks like I've been unknowingly riding my luck

Thanks

 

[jasonb75]

I think that you misunderstood what I was trying to say, but your post above indicates that the error will never be a problem anyway, so you can disregard this, but for clarification, should your times ever change and start giving incorrect results.

Although when the values are 1000, 0100 and 0030 the values are correct

Looking at the bold digits in the quote above, the total is only 30, 00+00+30, so not over 99 as you thought. The first 2 digits of each time (the hours) are ignored.
Even 1030+0100+0030 (which is the maximum possible from your data) only comes to 60.

For the problem that I noticed to occur, you would need at least one of the cells to have 45 as the last 2 digits. As far as I can work out the only combinations (cells in any order) that would cause the error would be

##45+##45+##45 =135
##45+##45+##30 =120
##45+##45+##15 =105
##45+##30+##30 =105

Where the # symbols represent any hour.

##45+##30+##15 =90 so is safe, as is ##30+##30+##30.

From your last post, I now understand exactly what you have, and get the same result from your current formula (I had H48:I48 incorrectly formatted as 00\:00)
Now that I can see it, I'll have another look at the formula later and see if I can simplify it.

edit:-

See if this works, only done a quick test

=--TEXT(H48-SUMPRODUCT(DOLLARDE(C48:E48/100,60))/24-I48-K48/24,"hh:mm;\0;0")

 

[August]

You're correct I didn't fully understand what you were trying to explain.
I will try your formula tomorrow and see if I can get it to work.
This is a great forum, but unfortunately always makes me realise that the gap between what I think I know and my actual knowledge is a little wider than I would like

Many thanks

 

[pjmorris]

It does that to all of us I think! ?

 

[August]

I have used your formula
=--TEXT(H48-SUMPRODUCT(DOLLARDE(C48:E48/100,60))/24-I48-K48/24,"hh:mm;\0;0")
and it works perfectly !!!
Now all i need to do is understand the formula, what else is a wet weekend for

Many, many thanks