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]

That can't be your actual formula, it contains too many arguments to be valid.

=MOD(existing formula,1) usually works to fix issues of times spanning midnight, but if you have #VALUE! rather than ###### then it will not work with what you have.

I'm sure that your formula could be fixed and simplified easily enough, but without the correct version of the formula it would just be guesswork.

Examples of the figures entered into all of the cells, along with the expected formula results would also be useful.

 

[pjmorris]

do your times also include date information?
its possible that you're calculating a negative time value, one cure for that would be to select 1904 as the date option: in Options - Advanced - 'When Calculating This Workbook' and then tick 'Use 1904 Date System'/

As Jason has said, it would help to have sample data together with the expected output.

 

[August]

apologies, i removed an if statement but kept the zero at the end, full formula is

=IF(CEILING(TEXT(G42,"00\:00")-TEXT(F42+C42+E42,"00\:00")-K42/24,0.5/24)>0,CEILING(TEXT(G42,"00\:00")-TEXT(F42+C42+E42,"00\:00")-0.0000001-K42/24,0.5/24),0)

 

[jasonb75]

In that case, some examples would be a necessity, I tried your formula with the zero at the end removed and got negative results continuously, regardless of what I entered in the precedent cells.

Given that 'wrap overtime' is not a term that I'm familiar with, a lack of understanding of how these figure would relate to the rest of the formula could be contributing.

 

[August]

I'm sure the formula could be simplified, any suggestions welcome.
Below is the table containing the data and formula
The formula is in L48
If the time entered in F48 is 1430 then i get a #value error, if time entered is 1330 I get no error
Thanks for taking the time

 

[jasonb75]

Could you explain the theory as well please?

I'm struggling to see how that should add up to 00:00, but regardless of that, I'm not getting #VALUE! when I enter the same data and your formula from post 4

I'm assuming that K48 is formatted to show - in place of zero?

Do any of the other cells, C48, E48, F48, G48 or K48 contain formulas?

 

[August]

again thank you for taking the time
Yes - is a format to show zero
Wrap is the overtime due based on the hours worked (H48) less the contracted hours (C48), less a wrap allowance of 30mins (E48), less any pre call overtime paid (I48)
Employee is called work @ 1730, they actually started @ 1430(F48) giving 3hrs pre call overtime, less an agreed contractual allowance of 1hr(D48) at the start of the day = total pre call of 2hrs (H48)
Employee finishes @0500 giving total hours worked 14hrs 30mins (G48)
Less contractual day of 10hrs(C48) leaves 4hrs 30mins
Less pre call overtime of 2hrs(I48) leaves 2hrs 30mins
Less agreed top of 1hr(D48) leave 1hr 30mins
Less agreed tail of 30mins(E48) leaves wrap overtime of 1hr in L48.

I use the 00\:00_- formatting to allow faster input of times (don't need to input a colon)
And for calculations that give a time result cells are formatted hh:mm_-

Having said all of that, this exercise has forced me to look at the overall calculations and I have simplified them somewhat and have elemenated the #value error.
I'm am still curious as to why a particular time would give that error when other times are fine.

Thank you all

 

[jasonb75]

I can't see anything from your example that would cause a #VALUE! error, which is the main reason that I asked for details of the cell contents earlier.
Typically that error would be caused by trying to use + - * / ^ or % with a text string that can not be converted to a number.
The only thing that stood out as being possible was an actual - being entered instead of a zero formatted as -, but that would have given the same error for 13:30 as well.

I'll have another look at it later, see if I can find any other reason for it. Possibly the way you're entering the times with the 00\:00 formatting could be the reason, I need to to a bit more testing to figure it out.

 

[pjmorris]

HI Jason,

The cunning formatting converts something like 1930 to 19:30 - in the cell the number remains as 1930 and the Text statements simply convert it to a string that looks like 19:30, Excel then coerces the text into a time value, as far as I can tell it then fails when you get something like "24:60". Oddly its happy to accept "24:59". I'm working on a conversion that doesnt rely on Excel's coercion capabilities.