Converting decimal hours into hh:mm

  • Thread starter Thread starter Lana
  • Start date Start date
L

Lana

Hello All

I am counting overtime for staff. I've got in and out columns and
counted the difference between them (it's in hh:mm format). I ca
convert it to decimal and substract 8 working hours from it, but m
boss wants it to look like hh:mm.
*So, the question is: if I have a 2.78hrs overtime, what do i do t
make it look like hh:mm? Any other suggestions?*

Thanks in advance.

Lana
 
Hi Lana!

Use:
With the difference in hours (as decimals) in A1:
=A1/24
Format hh:mm

But if you want the process in one operation:

With start time in A1 and end time in B1
=B1-A1+(A1>B1)-1/3

And I always smile on Fridays after the 3rd VB.
 
Hello All

I am counting overtime for staff. I've got in and out columns and I
counted the difference between them (it's in hh:mm format). I can
convert it to decimal and substract 8 working hours from it, but my
boss wants it to look like hh:mm.
*So, the question is: if I have a 2.78hrs overtime, what do i do to
make it look like hh:mm? Any other suggestions?*

Thanks in advance.

Lana
Click to expand...


DecimalHours / 24 and format as hh:mm

Or, when you do your subtracting, instead of converting, use:

= (Time in format hh:mm) - TIME(8,0,0)




--ron
 
Thanks guys!!!
That was SSSOOO quick - I was going to check for replies in th
afternoon and they are already waiting for me!

Everything works.

I'm gone to impress my boss with my fantastic efficiency.

Have a nice weekend all!

Regards

Lana
 
I didn't get that thing with =B1-A1+(A1>B1)-1/3.
It gives me 1 hr for the times when precise working day has been done


The B1 is always more than A1. But if the total worked hours are les
than 8, I need to show it as UNDER time:
example
start: 9:00
end: 17:00 (should be 18:00). There is an hour lunch time also ;o(
-1hrs overtime.


Lana
 
Hi Lana!

=B1-A1+(A1>B1)-1/3

=B1-A1
Returns the difference between the finish time and the start time.

=(A1>B1)
An implicit IF statement that returns 1 if the start time is later
than the end time; this covers a case where you start before midnight
and end after midnight. If this can never arise, you can delete it.

=-1/3
Time is recorded as a decimal part of 1 day, so 1/3 represents 8
hours. A more user friendly approach would have been to use 8/24.

You can handle the lunchtime problem in various ways but I suggest
that you take a look at:

Chip Pearson:
http://www.cpearson.com/excel/overtime.htm
 
I'm not sure if this is what you are asking but 2.75 in
overtime would be 2 hrs 45 minutes so 2.78 should be 2 hrs
48 minutes. another example 2.25 is 2 hrs 15 minutes.
Hope this is what you need?? TJG
 
Anonymous said:
I'm not sure if this is what you are asking but 2.75 in
overtime would be 2 hrs 45 minutes so 2.78 should be 2 hrs
48 minutes. another example 2.25 is 2 hrs 15 minutes.
Hope this is what you need?? TJG
Click to expand...
 
Back
Top