Time formula

  • Thread starter Thread starter Nelson
  • Start date Start date
N

Nelson

Hello, can anyone help with a formula that will divide a
span of time into 4 range and sum the total hours. For
example the span of time is
Cell 1: 9:00AM Cell 2: 4:00PM

That would be divided into 4 ranges:
12:01AM - 12:00PM = 3hrs
12:01PM - 3:00PM = 3hrs
3:01PM - 6:00PM = 1hr
6:01Pm - 12:00AM = 0hrs

Now sum each of the ranges for all rows.

All suggestion are welcome, worksheet functions or code.

TIA
Nelson
 
Hi Nelson,

Can you cross midnight?
In other words, can the finishing time be LOWER than the starting time
(starting @ 23:00 and finishing @ 2:00 AM) ?

Regards,

Daniel M.
 
No, you can't cross Midnight. Think of this as a work
schedule where you have a group of people and you want to
know how many hours a group of people are available within
each of the 4 ranges.
emp1 available 9-4 =3hrs 9-12; 3hrs 12-3; 1hr 3-6
emp2 avilable 10-4 =2hrs 9-12; 3hrs 12-3; 1hr 3-6
emp3 available 3-6 =0hrs 9-12; 0hrs 12-3; 3hr 3-6

9-12 = 5hrs
12-3 = 6hrs
3-6 = 5hrs

Thanks
 
Hi,
No, you can't cross Midnight.
Click to expand...
Ok.
Here's how I would do it.
In C1:F1 0:00 12:00 15:00 18:00
In C2:F2 12:00 15:00 18:00 24:00

Your start time in A3 9:00 AM
Your end time in B3 4:00 PM

In C3, the following formula:
=MAX(0,MIN($B3,C$2)-MAX($A3,C$1))

Copy C3 to D3:F3

C3:F3 contains your split time by intervals.

Advantage of that method is you can change your intervals (for whatever
reasons) in C1:F2 and your formulas will react appropriately (no hard
dependencies within the formulas themselves).

Regards,

Daniel M.
 
Thanks, this is helps. This requires 4 additional columns
for each day of the week. 28 more columns. Is there any
way to sum all of the times first. For example, if you
have 2 start times at 9am, the sum in the 0-12 range would
be 6. Should I consider a custom function for the total
for each of the 4 time ranges.

Thanks
 
Hi,
Thanks, this is helps. This requires 4 additional columns
for each day of the week. 28 more columns. Is there any
Click to expand...

Never heard before about "each day of the week" in a different column.

Don't take this too heavily but _you_ have the responsibility to detail how
your data is structured.
I can't guess in advance! Only heard about a starting and ending time and
that you wanted to split it in 4 time slices.

So please provide the data structure (what info in what column) so that
I/others
can answer you.
Or send me an email file of an example of your data (just replace
prenom.maher with daniel.maher and remove the "inutil." in the domain name).

Regards,

Daniel M.
 
Back
Top