Question

Using MROUND to round by multiples of .5 when calculating hours worked

Forms

Updated August 9, 2019

asked on August 7, 2019

I have a Night Differential column in a table to report overtime hours worked. In the Night Differential column, I only count hours worked between 00:00-24:00 that are prior to 06:00 (6AM) and after 18:00 (6PM). The calculation below works OK.

=IF(AND(INDEX(OT_Table.End_Time,ROW())<>"",TIMEVALUE(INDEX(OT_Table.Start_Time,ROW()))<0.25,TIMEVALUE(INDEX(OT_Table.End_Time,ROW()))<=0.75),(0.25-VALUE(INDEX(OT_Table.Start_Time, ROW())))*24,
IF(AND(INDEX(OT_Table.End_Time,ROW())<>"",TIMEVALUE(INDEX(OT_Table.Start_Time,ROW()))>=0.25,TIMEVALUE(INDEX(OT_Table.End_Time,ROW()))>0.75),(VALUE(INDEX(OT_Table.End_Time, ROW()))-0.75)*24,
IF(AND(INDEX(OT_Table.End_Time,ROW())<>"",TIMEVALUE(INDEX(OT_Table.Start_Time,ROW()))<0.25,TIMEVALUE(INDEX(OT_Table.End_Time,ROW()))>0.75),((0.25-VALUE(INDEX(OT_Table.Start_Time, ROW())))*24)+((VALUE(INDEX(OT_Table.End_Time, ROW()))-0.75)*24),
0)))

What I'm trying to do is round up to the nearest half hour, so... by multiples of .5. I tried the following with no luck. It doesn't return any number, just blank.

=IF(AND(INDEX(OT_Table.End_Time,ROW())<>"",TIMEVALUE(INDEX(OT_Table.Start_Time,ROW()))<0.25,TIMEVALUE(INDEX(OT_Table.End_Time,ROW()))<=0.75),(0.25-MROUND(VALUE(INDEX(OT_Table.Start_Time, ROW())),.5)*24, ... (and so on)

LF_Forms_NightDiff_rounding.png (45.7 KB)

| Download

0 0

Replies

replied on August 8, 2019

Hi Mandy,

".5" is not a valid number; can you try "0.5" instead?

1 0

replied on August 9, 2019

It didn't work correctly. It returns "6", no matter what times are entered.

0 0

replied on August 9, 2019

I got it to work by putting the MROUND outside the fraction of day to hours calculations.

=IF(AND(INDEX(OT_Table.End_Time,ROW())<>"",TIMEVALUE(INDEX(OT_Table.Start_Time,ROW()))<0.25,TIMEVALUE(INDEX(OT_Table.End_Time,ROW()))<=0.75),MROUND((0.25-VALUE(INDEX(OT_Table.Start_Time, ROW())))*24,0.5),
IF(AND(INDEX(OT_Table.End_Time,ROW())<>"",TIMEVALUE(INDEX(OT_Table.Start_Time,ROW()))>=0.25,TIMEVALUE(INDEX(OT_Table.End_Time,ROW()))>0.75),MROUND((VALUE(INDEX(OT_Table.End_Time, ROW()))-0.75)*24,0.5),
IF(AND(INDEX(OT_Table.End_Time,ROW())<>"",TIMEVALUE(INDEX(OT_Table.Start_Time,ROW()))<0.25,TIMEVALUE(INDEX(OT_Table.End_Time,ROW()))>0.75),
MROUND(((0.25-VALUE(INDEX(OT_Table.Start_Time, ROW())))*24)+((VALUE(INDEX(OT_Table.End_Time, ROW()))-0.75)*24),0.5),
0)))

0 0

You are not allowed to follow up in this post.

Question

Question

Using MROUND to round by multiples of .5 when calculating hours worked

Replies

Sign in to reply to this post.