Hi all,
I have a spreadsheet that calculates how many hours overtime each staff member works on a particular day. However it is not returning the correct result. I have attached the worksheet. Cell E30 would be a good example. George doesn't work Mondays however he is working 8 hours. The cell continues to return 0 however. Any help would be greatly appreciated.
Thanks,
Lukewc 10 November 2013.xls
Last edited by lukela85; 10-31-2013 at 10:26 AM.
Merged cells do not work well with looping through a cell. I do not know if this is the case with formula though.
It seems the ISNUMBER function does not recognise time in the format [h]:mm? Would this be why it always returns 0? The cells are all conditionally formatted to change all 0 returns to a white font color.
Hi,
Not sure why you think this. To me it's perfectly logical that this cell is returning 0, since you are using the ROUND function with a num_digits parameter of 0 on a value of 0.333333333333333, which evaluates to 0.
Remove the ROUND function and the value in cell E30 is 8:00, as desired.
Regards
Thanks. All solved. My knowledge of Excel is very basic and I wasn't aware of significance of the num_digits parameter.
Are you trying to round the result to the nearest hour?
To the nearest 15 minutes. With a num_digits parameter of 15 it is currently doing this.
Are you sure?Originally Posted by lukela85
The syntax to round a time value to nearest 15 minutes would be
=ROUND(TIME*24*4,0)/4/24
Where time is the result of your existing formula without the round function.
Yes. Be careful. A num_digits parameter of 15 does not at all mean that it is rounding to the nearest 15 minutes.
This parameter is the number of decimal digits to which to round (and will not be affected by the number format of the cell).
Regards
I am using the following formula to calculate whether a staff member works any extra hours each day. However sometimes it returns a negative value and I have the cell in [h]:mm format so it doesn't return a value.
=ROUND(IF(ISNUMBER(G39),H39-G39-'Hours Matrix'!D26-IF(AND(G39<0.500694444,H39>0.60416667),1/24,0),0),15)
Using the formula above the staff member works from 12:00 until 17:00 which is her contract hours, so I would expect the formula to return 0. However it is returning a value of -0.04. Based on the responses in the posts above I'm using the wrong syntax however I'm not sure how to include this within the formula above.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks