Calculate between two time formats

[john86]

i I am working on a spreadsheet, where I need to know how to Calculate
between two time formats, i.e. I want the cell to calculate between 06:00 and
18:00 and 18:00 and 06:00. If some one starts work at say 17:00 and finished
at 07:00 the following day, I need it to tell me how may hours before 18:00,
then how many hours between 18:00 and 06:00, the how many hours after 06:00.
If some one could help.

Thanks John

[Jason Morin]

A2: Start time
B2: End time

Time before 18:00:

=IF(A2>--"18:00",0,"18:00"-A2)

Time between 18:00 and 6:00:

=MIN(--"6:00",B2)-MAX(--"18:00",A2)+(MIN(--"6:00",B2)<MAX
(--"18:00",A2))

Time after 6:00:

=IF(B2<--"6:00",0,B2-"6:00")

This of course assumes that the start time is always in
the PM and the end time is always in the AM.

HTH
Jason
Atlanta, GA


>-----Original Message-----
>i I am working on a spreadsheet, where I need to know
how to Calculate
>between two time formats, i.e. I want the cell to
calculate between 06:00 and
>18:00 and 18:00 and 06:00. If some one starts work at
say 17:00 and finished
>at 07:00 the following day, I need it to tell me how may
hours before 18:00,
>then how many hours between 18:00 and 06:00, the how
many hours after 06:00.
>If some one could help.
>
>Thanks John
>
>.
>

[Peo Sjoblom]

To get the night shift hours (18:00 - 06:00)

With start time in A1 and end time in B1
06:00 in A2 and 18:00 in B2

to get the night shift hours

=MAX(0,MIN(B1,A2)-IF(B1>A1,A1,MIN(0,A1-A2)))+MAX(0,1-MAX(B2,A1)-IF(B1>A1,1-B
1,MIN(0,B2-B1)))

to get the day shift hours

=MOD(B1-A1,1)-(MAX(0,MIN(B1,A2)-IF(B1>A1,A1,MIN(0,A1-A2)))+MAX(0,1-MAX(B2,A1
)-IF(B1>A1,1-B1,MIN(0,B2-B1))))

--

Regards,

Peo Sjoblom



"john86" <[email protected]> wrote in message
news:[email protected]...
> i I am working on a spreadsheet, where I need to know how to Calculate
> between two time formats, i.e. I want the cell to calculate between 06:00
and
> 18:00 and 18:00 and 06:00. If some one starts work at say 17:00 and
finished
> at 07:00 the following day, I need it to tell me how may hours before
18:00,
> then how many hours between 18:00 and 06:00, the how many hours after
06:00.
> If some one could help.
>
> Thanks John
>

[Rob van Gelder]

I have an example on my website which handles many scenarios (including
spans over midnight)
Hours affected by dates

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"john86" <[email protected]> wrote in message
news:[email protected]...
>i I am working on a spreadsheet, where I need to know how to Calculate
> between two time formats, i.e. I want the cell to calculate between 06:00
> and
> 18:00 and 18:00 and 06:00. If some one starts work at say 17:00 and
> finished
> at 07:00 the following day, I need it to tell me how may hours before
> 18:00,
> then how many hours between 18:00 and 06:00, the how many hours after
> 06:00.
> If some one could help.
>
> Thanks John
>