Thread
:
Complicated Time Formula
View Single Post
#
9
Posted to microsoft.public.excel.programming
Norman Harker
external usenet poster
Posts: 162
Complicated Time Formula
Hi Kevin!
Bin there, done that!
Seems to check out OK.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Kevin Beckham" wrote in message
...
Too much haste, corrected version
=IF(StartTime<D2,IF(FinishTimeD3,60,IF(FinishTime <D2,0,
(FinishTime-D2)*24*60)),IF(StartTimeD3,0,IF(FinishTimeD3,
(D3-StartTime)*24*60,(FinishTime-StartTime)*24*60)))
Kevin Beckham
-----Original Message-----
Hi Kevin!
I think you may get an error with (e.g) start time = 9:00
finish time
= 9:45 (i.e. start and finish in the same hour slot.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Excel and Word Function Lists (Classifications, Syntax
and Arguments)
available free to good homes.
"Kevin Beckham"
wrote in message
...
To use the formula below, you will need to do a couple
of
things first
1. insert two new rows, the first will contain the start
time for an interval,
the second will contain the finish time for the
interval
e.g. below 9-10, the first cell will contain 09:00
the next cell will contain 10:00
2. name the start time cell as StartTime and the finish
time as FinishTime
now in the cells below these interval starts and
finished,
add the following formula
(assuming this formula will go in a cell in the C
column,
and that row 2 contains the interval start times,
and row 3 contains the interval finish times
=IF(StartTime<C2,IF(FinishTimeC3,60,IF(FinishTime <C2,0,
(FinishTime-C2)*24*60)),IF(StartTimeC3,0,(C3-StartTime)
*24*60))
The 24*60 converts the numbers from fractions of a day
into minutes
Kevin Beckham
-----Original Message-----
I have a comlicated formula that I need to develop and
I'm looking for some help. I have two cells that
contain
a start time and an end time. I need to calculate the
total time between the two times and place the value
into
the approipriate time slot. i.e. if the start time is
09:15 and the end time is 11:30 the value in the 09-10
cell would be 45 and the value in the 10-11 cell would
be
60, and the value in the 11-12 cell would be 30. Any
ideas or hints. Thank you.
.
.
Reply With Quote
Norman Harker
View Public Profile
Find all posts by Norman Harker