Need Help With Rounding in Timecard

[fher]

Attachment is a timecard I created and need help with adding round to the formulas. There are examples in there, so please help.

[joeu2004]

Attachment is a timecard I created and need help with adding round to the formulas.

I infer that you want to round to the half hour. If that's the case, enter the following formulas and copy down the columns.

Please Login or Register  to view this content.

Explanation.... Excel time is stored as a fraction of a day. Multiplying by 48 (24*2) converts Excel time to the number of half hours, which we round. Dividing the rounded number of half hours by 48 converts back to Excel time.

[fher]

I infer that you want to round to the half hour. If that's the case, enter the following formulas and copy down the columns.

Please Login or Register  to view this content.

Explanation.... Excel time is stored as a fraction of a day. Multiplying by 48 (24*2) converts Excel time to the number of half hours, which we round. Dividing the rounded number of half hours by 48 converts back to Excel time.

Thank you for fast replied. Away from my pc now so will check it later but if I want a15 mins block, I can just replace "00:15" for the 48 right?

[FlameRetired]

This has 8 helper columns. The formulas are all of the same general form.

Please Login or Register  to view this content.

They express the times in minutes elapsed.

Then starting in D2 this general form of rounded times continues to the next 2 columns. This is an array entered formula.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

When finished format h:mm.

The file is attached.

[joeu2004]

Please Login or Register  to view this content.

if I want a15 mins block, I can just replace "00:15" for the 48 right?

Let's look at an example....

Instead of ROUND((MOD(C2-B2,1)-D2-E2)*48,0)/48, we might write:

ROUND((MOD(C2-B2,1)-D2-E2)*1440/15,0)*15/1440
or
ROUND((MOD(C2-B2,1)-D2-E2)*96,0)/96

(Note that 1440/30 = 48.)

Hypothetically, if we want to use time constants like "00:15", we might write:

MROUND((MOD(C2-B2,1)-D2-E2),"00:15")

However, I deprecate the use of MROUND when the second parameter is not an integer. (Note that 00:15 = 0.25.) Infinitesimal binary arithmetic differences can arise, which might cause problems in dependent cells. For example:

A1: =MROUND("12:30","00:15")
B1: =MATCH(TIME(12,30,0),A1,0)

A2: =ROUND("12:30"*1440/15,0)*15/1440
B2: =MATCH(TIME(12,30,0),A2,0)

B1 returns #N/A because the exact match fails. B2 returns 1 because the exact match succeeds.

The binary difference between A1 and A2 is so small, we cannot see it even when formatted as Number with 16 decimal places because Excel formatting is limited to 15 significant digits.

But MATCH does a binary comparison; so the infinitesimal difference affects MATCH.

Aside.... We also cannot detect the binary difference if we write simply =A1-A2 or =(A1=A2). Both create the illusion that there is no difference because sometimes, Excel treats two values as exactly the same when their difference is "close to zero". Instead, we must write =A1-A2-0 formatted as Scientific or =(A1-A2=0), which defeat the dubious and inconsistently-applied "close to zero" heuristic.

[fher]

Thank you for all of you for helps. I use the *96,0)/96 to get the 15mins and it worked great but had another problem. Please check the new attachment here and see if the problem can be solved.