Calculating scheduled minutes in a time block

[sparkhil]

I am trying to take scheduled start time and scheduled duration in h:mm and auto populate the scheduled minutes in the corresponding time blocks.

Example:

Schedule ID	Start Time	Duration	8AM	9AM	10AM	11AM	12PM	1PM	2PM	3PM	4PM
Schedule A	8:45 AM	00:03:00	15	60	60	45
Schedule B	12:00 PM	00:04:00					60	60	60	60
Schedule C	3:00 PM	00:01:15								60	15

<tbody>
</tbody>

The first 3 columns are derived from a data table and I am trying to auto populate the minutes scheduled in the time blocks using conditional formatting or some other mechanism.

Any insight would be greatly appreciated.

I'm using Excel 2010

 

[iliace]

This should get you started.

Sheet2

	B	C	D	E	F	G	H	I	J	K	L	M
2	Schedule ID	Start	Duration	8:00	9:00	10:00	11:00	12:00	13:00	14:00	15:00	16:00
3	Schedule A	8:45	3:00	15	60	60	45	0	0	0	0	0
4	Schedule B	12:00	4:00	0	0	0	0	60	60	60	60	0
5	Schedule C	15:00	1:15	0	0	0	0	0	0	0	60	15

<tbody>
</tbody>

Worksheet formulas

Cell Formula E3 =ROUND(MAX(0, (MIN($C3+$D3, F$2)-MAX(E$2, $C3))*24*60), 0) <tbody> </tbody>

<tbody>
</tbody>

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Notes
* Copy cell E3 for the entire range
* ROUND() is added to avoid tiny fractions, like 0.0000000015
* Use number format to avoid 0 display, or an IF function if you prefer
* The *24*60 part is to convert Excel's Date/Time value to minutes (24 hours in a day, 60 minutes in an hour)
* You can change the intervals at the top as you wish, to get the correct result (for example, try changing 9:00 to 9:30), however be mindful that they don't overlap (e.g. changing 9:00 to 10:30 still works, but doesn't make sense).

Good luck.

 

[sparkhil]

That worked perfectly. Thanks so much for your help, it's greatly appreciated!

 

[sparkhil]

One more question on the formula, I hope someone can help with...is there a way to display the zero value fields as a blank, instead of 0?

 

[iliace]

In the Notes above:

* Use number format to avoid 0 display, or an IF function if you prefer

For the number format option, format your cells as Custom, for example 0;-0;;@

This will preserve the 0 result, but display a blank instead.

for the IF option, you would do this

=IF(formula=0, "", formula)

Where formula is the one from E3 above: =ROUND(MAX(0, (MIN($C3+$D3, F$2)-MAX(E$2, $C3))*24*60), 0)

This will display a blank, therefore not recognize those cells as a number (zero).


Your choice will determine how these cells are used in certain calculations, such as AVERAGE() function.

 

[sparkhil]

In the Notes above:


For the number format option, format your cells as Custom, for example 0;-0;;@

This will preserve the 0 result, but display a blank instead.

for the IF option, you would do this

=IF(formula=0, "", formula)

Where formula is the one from E3 above: =ROUND(MAX(0, (MIN($C3+$D3, F$2)-MAX(E$2, $C3))*24*60), 0)

This will display a blank, therefore not recognize those cells as a number (zero).


Your choice will determine how these cells are used in certain calculations, such as AVERAGE() function.

I went with the =IF(formula=0, "", formula) option, as I need to count non zero values in a pivot table and this seemed like the best way to achieve that. I thought we had it nailed, but noticed some anomalies where a 0 is returned in the column to the left of the first numeric value. For some reason this does not happen on all rows.

Example:

Integration/ Report Name

Scheduled Start Time

Avg Run Duration

End Time

Day of Week

12:01 AM

12:30 AM

1:00 AM

1:30 AM

2:00 AM

2:30 AM

3:00 AM

3:30 AM

4:00 AM

4:30 AM

5:00 AM

5:30 AM

6:00 AM

6:30 AM

7:00 AM

7:30 AM

INT201

1:00 AM

4:27:00

5:27 AM

Daily

30

30

30

30

30

30

30

30

27

INT202

3:30 AM

2:17:00

5:47 AM

Monday

30

30

30

30

17

INT203

4:00 AM

0:36:00

4:36 AM

Tuesday

0

30

6

INT204

6:00 AM

0:45:00

6:45 AM

Wednesday

30

15

<tbody>
</tbody>

The formula I'm using is =IF(MAX(0,(MIN($B2+$C2,J$1)-MAX(I$1,$B2))*48*30)=0,"",ROUND(MAX(0,(MIN($B2+$C2,J$1)-MAX(I$1,$B2))*48*30),0))

Any insight on how I might modify the formula above to eliminate the 0 value population would be greatly appreciated, as this inflates the column instance counts (3:30 AM should have an instance count of 2, but returns 3 due to the 0 value).

 

[iliace]

I did not get that result; my 3:30AM for INT203 is showing a blank.

However, I suspect the problem might be that you're using the ROUND() function in the FALSE portion of the IF statement, rather than the condition. Try this instead (using your cell addresses):


=IF(ROUND(MAX(0,(MIN($B2+$C2,J$1)-MAX(I$1,$B2))*48*30),0)=0,"",MAX(0,(MIN($B2+$C2,J$1)-MAX(I$1,$B2))*48*30))


Again, the problem might be tiny fractions due to binary rounding issues. Because time is a fraction of 1, it can result in some really small values, and instead of a 0, you might be getting results such as 0.0000000015. The condition part of the IF function is looking for an exact 0, and if the time calculation evaluates to these fractional values instead, the FALSE portion of the IF statement is returned. That part returns a number, regardless of the rounding.

If instead you move the ROUND() function to the condition part of the IF statement, you avoid these rounding errors, and get the correct results.

That's my hunch. Hope it works out for you, post back if not.

 

[ktcharm]

Sorry Sparkhil, don't mean to hijack your thread. Iliace, if you read this thread can you please assist with mine? I have something very similar to the original poster request, but my time went over to the next day past midnight. Thanks for your help.


http://www.mrexcel.com/forum/excel-...ng-scheduled-minutes-time-block-overtime.html