Complicated Time Formula

[Jay]

Wow thank you for all the help.
What my utilmate goal is to determine a utilization ratio
for an ambulance service I.e. if an ambulance was on a
call from 23:15 to 00:15, I want to be able to show that
that ambulance was active for 75% of the 23:00 time block
and 25% of the 00:00 time block. This easy to do when
dealing with one call, but I am dealing with several
thousand over a month long period. Once I am able to
determine the amount of time spent on each call in each
time block the rest is very easy to complete. Thank you
again. You guys have been great. The main hang up is the
00:00 time block.

Jay

 

[Jay]

Rob,
Okay. that is quite a few differant cominations. Would it
be at all helpfull if I was able to include the dates in
the start and end times? I do have that data available and
can incorperate it if need be. The question is I do not
want to incorperate the dates into the results, as I only
need a total time in each time block for my end result. Jay

 

[Norman Harker]

Oh my word yes!!!!!

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[JAY]

Suddenly I wish I had said I could do that earlier. Okay
so I can do one of two things. I can write a simple
formula to combine the two date and times into one or I
can use four cells.
i.e.
A2=start date
B2=start time
C2=end date
d2=end time
I did not mention this earlier because I didn't really
feel it would be needed. Sorry.
Thank You so much for the time and effort.

 

[Norman Harker]

Hi JAY!

I'll take a look when my toothache goes away enough! But it looks
pretty straightforward now thanks to ability to have date added to the
time.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Jay]

Hi Norm,
If you were in the states I'd drive you to the dentist.
Thank you! Jay

 

[Norman Harker]

Hi Jay!

Send a copy of the workbook you have to me and I'll play about with
it. Should be faster that way.

Rinsing mouth with Single Malt seems to be quite good for tooth ache.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi Jay!

I think that I've got it working OK now.

A1: Start Time
B1: Finish Time
A2: Named StartTime
A2 Sample entry: 30-Dec-2003 8:30
B2: Named FinishTime
B2 Sample entry: 30-Dec-2003 12:30
C1:
=DATE(YEAR(StartTime),MONTH(StartTime),DAY(StartTime))
D1:
=C1+1/24
Copied across to AZ1 (i.e. covers 2 days + 1 hour)
C2:
=IF(StartTime<C1,IF(FinishTime>D1,60,IF(FinishTime<C1,0,(FinishTime-C1
)*24*60)),IF(StartTime>D1,0,IF(FinishTime>D1,(D1-StartTime)*24*60,(Fin
ishTime-StartTime)*24*60)))
Copied across to AY2 (note that the row 1 time overlaps the row 2
calculation by 1 hour (this prevents a problem with the final cell
calculation)

I've tested for the difficult problems of starting and finishing
within the hour and with over-lapping midnight and / or noon and it
seems OK

I'll send workbook if you want but you can construct from the above.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Jay]

Norm,
I am glad you have found the cure for toothaches. Could
you send me the workbook. I must be doing something wrong
as I am getting errors in every cell from K2 on. My email
address is listed below. Also is this going to work if i
copy it down to perform this function for several hundred
sets if times?

Thank you
Jay
(e-mail address removed)

 

[Norman Harker]

Hi Jay!

Have done!

I think it was the Single Malt that cured the toothache.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi Jay!

There will be some amendments need to facilitate copying down. But
nothing too serious. Let's get it working once first <vbg>

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Jay]

No words can describe how greatfull I am. Thank You. Now I
just need to get it working for several hundred rows. The
catch is there is going to be a months worth of dates to
work off of. I am going to end up summing each column for
each time slot. So they would need to be constant. Which
causes a problem with the dates in the first row.
Jay

 

[HÉCTOR E. POLLA]

here i send my offer without macros. Sorry for my english.
HEP

*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.

Attachment filename: problema complejo de horarios.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=39367

 

[Norman Harker]

Hi Hector!

We got it all working OK off newsgroup in the end.

The key formula was:

=IF(C1="","",IF($A2<C1,IF($B2>D1,60,IF($B2<C1,0,($B2-C1)*24*60)),IF($A
2>D1,0,IF($B2>D1,(D1-$A2)*24*60,($B2-$A2)*24*60))))

A2 contained the start date and time and B2 contained the stop date
and time.

We managed a copy down OK and a summing of the results.

The file of 10000 records is a bit big but I've suggested using
monthly files and then a summary file.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.