Converting Millitary Time to Hours worked and OT hours

[Djbaker70]

What I'm trying to do is. Take 700 start time to an end time of 1700 then I
need it to convert it to hours worked then the next cell show hours no more
then 8 in the next cell any thing over the 8 hours. Start time would be one
cell, End time is one cell, Total Hours is one sell, Hours no more them 8 is
one cell, overtime hours is one cell. Then I have to Multiply the 8 hours in
the next cell and then the ot hour in the next cell. Any help would be fine.
Thanks in advance

 

[Peo Sjoblom]

Do you want time of integers? meaning if you have start at 700 and end
at1730 do you want to display that
as 2.5 hours over of 2:30? Assume you want time values and not decimals

Anyway assume you have start in A2, end in B2, then the first up to 8 hours
in C2 and the rest in D2

in C2 put

=MIN("8:00",--TEXT(B2-A2,"00\:00"))

in D2 put

=MAX(0,TEXT(B2-A2,"00\:00")-"8:00")


format as time [h]:mm



now if you want decimal values in C2 use

=MIN(8,24*TEXT(B2-A2,"00\:00"))


=MAX(0,24*TEXT(B2-A2,"00\:00")-8)

format as general or number

If start can be for instance 1500 and end 300 meaning start is before
midnight and end after then you can use

=MIN("8:00",MOD(TEXT(B2,"00\:00")-TEXT(A2,"00\:00"),1))

and


=MAX(0,MOD(TEXT(B2,"00\:00")-TEXT(A2,"00\:00"),1)-"8:00")

to get decimals do t


=MIN(8,24*MOD(TEXT(B2,"00\:00")-TEXT(A2,"00\:00"),1))


and

=MAX(0,24*MOD(TEXT(B2,"00\:00")-TEXT(A2,"00\:00"),1)-8)


--


Regards,


Peo Sjoblom

 

[Djbaker70]

Peo, Here is what I have
Cell D5 7:00 Cell E5 20:30 F5 20:30 and it should display 20.5 Then G5
should show 8 for hours or less, & H5 should show the over time. Yes you are
right that I want the time to show a half hour as .5 not :30
Thanks in advance.
Dennis

Do you want time of integers? meaning if you have start at 700 and end
at1730 do you want to display that
as 2.5 hours over of 2:30? Assume you want time values and not decimals

Anyway assume you have start in A2, end in B2, then the first up to 8 hours
in C2 and the rest in D2

in C2 put

=MIN("8:00",--TEXT(B2-A2,"00\:00"))

in D2 put

=MAX(0,TEXT(B2-A2,"00\:00")-"8:00")


format as time [h]:mm



now if you want decimal values in C2 use

=MIN(8,24*TEXT(B2-A2,"00\:00"))


=MAX(0,24*TEXT(B2-A2,"00\:00")-8)

format as general or number

If start can be for instance 1500 and end 300 meaning start is before
midnight and end after then you can use

=MIN("8:00",MOD(TEXT(B2,"00\:00")-TEXT(A2,"00\:00"),1))

and


=MAX(0,MOD(TEXT(B2,"00\:00")-TEXT(A2,"00\:00"),1)-"8:00")

to get decimals do t


=MIN(8,24*MOD(TEXT(B2,"00\:00")-TEXT(A2,"00\:00"),1))


and

=MAX(0,24*MOD(TEXT(B2,"00\:00")-TEXT(A2,"00\:00"),1)-8)


--


Regards,


Peo Sjoblom

What I'm trying to do is. Take 700 start time to an end time of 1700 then
I
need it to convert it to hours worked then the next cell show hours no
more
then 8 in the next cell any thing over the 8 hours. Start time would be
one
cell, End time is one cell, Total Hours is one sell, Hours no more them 8
is
one cell, overtime hours is one cell. Then I have to Multiply the 8 hours
in
the next cell and then the ot hour in the next cell. Any help would be
fine.
Thanks in advance

 

[Peo Sjoblom]

Just multiply the formula with 24 and format as general

=24*(end-start)

important to format as general or number not time

then just

=MIN(8,24*(end-start))

and

=MAX(0,24*(end-start)-8)


--


Regards,


Peo Sjoblom

Peo, Here is what I have
Cell D5 7:00 Cell E5 20:30 F5 20:30 and it should display 20.5 Then G5
should show 8 for hours or less, & H5 should show the over time. Yes you
are
right that I want the time to show a half hour as .5 not :30
Thanks in advance.
Dennis

Do you want time of integers? meaning if you have start at 700 and end
at1730 do you want to display that
as 2.5 hours over of 2:30? Assume you want time values and not decimals

Anyway assume you have start in A2, end in B2, then the first up to 8
hours
in C2 and the rest in D2

in C2 put

=MIN("8:00",--TEXT(B2-A2,"00\:00"))

in D2 put

=MAX(0,TEXT(B2-A2,"00\:00")-"8:00")


format as time [h]:mm



now if you want decimal values in C2 use

=MIN(8,24*TEXT(B2-A2,"00\:00"))


=MAX(0,24*TEXT(B2-A2,"00\:00")-8)

format as general or number

If start can be for instance 1500 and end 300 meaning start is before
midnight and end after then you can use

=MIN("8:00",MOD(TEXT(B2,"00\:00")-TEXT(A2,"00\:00"),1))

and


=MAX(0,MOD(TEXT(B2,"00\:00")-TEXT(A2,"00\:00"),1)-"8:00")

to get decimals do t


=MIN(8,24*MOD(TEXT(B2,"00\:00")-TEXT(A2,"00\:00"),1))


and

=MAX(0,24*MOD(TEXT(B2,"00\:00")-TEXT(A2,"00\:00"),1)-8)


--


Regards,


Peo Sjoblom

What I'm trying to do is. Take 700 start time to an end time of 1700
then
I
need it to convert it to hours worked then the next cell show hours no
more
then 8 in the next cell any thing over the 8 hours. Start time would be
one
cell, End time is one cell, Total Hours is one sell, Hours no more them
8
is
one cell, overtime hours is one cell. Then I have to Multiply the 8
hours
in
the next cell and then the ot hour in the next cell. Any help would be
fine.
Thanks in advance