Days, Hours, Minutes

  • Thread starter Thread starter David Turner
  • Start date Start date
D

David Turner

My paycheck stub lists Vacation and Sick Leave as hours like this:

Vac 64.29
Sick 480.18

If I put those values in B2:B3, what formulas do I need in C3:C4 to produce

X Days, X Hours, X Minutes
 
David Turner wrote
My paycheck stub lists Vacation and Sick Leave as hours like this:

Vac 64.29
Sick 480.18

If I put those values in B2:B3, what formulas do I need in C3:C4 to
produce

X Days, X Hours, X Minutes

Oops, that should be C2:C3
 
David -

Is a day 8 hours? Put this formula into C2, then drag it down to fill
C3, to get your answer:

=INT(B2/8)&" Days, "&INT(B2-INT(B2/8)*8)&" Hours,
"&TEXT((B2-INT(B2))*60,"0.00")&" Minutes"

The formula fits all on one line. Here are the results in C2:C3

8 Days, 0 Hours, 17.40 Minutes
60 Days, 0 Hours, 10.80 Minutes

- Jon
 
Jon Peltier wrote
David -

Is a day 8 hours?
Yes.

=INT(B2/8)&" Days, "&INT(B2-INT(B2/8)*8)&" Hours,
"&TEXT((B2-INT(B2))*60,"0.00")&" Minutes"

The formula fits all on one line. Here are the results in C2:C3

8 Days, 0 Hours, 17.40 Minutes
60 Days, 0 Hours, 10.80 Minutes

Thank you. I do find it implausable, though, that I used fractions of
minutes when I took Annual or Sick Leave <g>.
 
Myrna Larson wrote
He worked with the data you showed. For one of them the fractional
part was 0.29. 0.29 days * 8 hours per day = 2.32 hours = 139.2
minutes = 2 hours 19.2 minutes.

You should ask your payroll department how they round your times -- to
the nearest 5 minutes, 10 minutes, 15 minutes? If 5 minutes, the
fractional part should always be a multiple of 1/12/8 = 1/96; if 10
minutes, a multiple of 1/6/8 = 1/48; if 15 minutes, a multiple of
1/4/8 = 1/32.

Indeed, I wasn't questioning his solution, but payroll's way of calculating
time.
 
Myrna Larson wrote
He worked with the data you showed. For one of them the fractional
part was 0.29. 0.29 days * 8 hours per day = 2.32 hours = 139.2
minutes = 2 hours 19.2 minutes.

Actually, it was .29 hours, as reflected in my original post:
"My paycheck stub lists Vacation and Sick Leave as hours like this"

Your point is still well taken. I just found parts of minutes curious.
 
Back
Top