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
"Djbaker70" <(E-Mail Removed)> wrote in message
news:0FCFC59F-5323-4254-BFCA-(E-Mail Removed)...
> 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
>
> "Peo Sjoblom" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:67A5E114-F351-490B-B8ED-(E-Mail Removed)...
>> > 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
>>
>>
>>
|
Similar Threads
