I I have created a word report linking 9 Excel workbooks each using 52
worksheets (Annual) Each workbook also has a calculation worksheet. I have
been requested by my manager the following.
Service annual hours = 52000 hrs cell A1
1) I need cells B1,B2,B3 to split cell A1 equally for a the three districts
(annual)
2) Then each district say C1,C2,C3,C4 divide cell B1 (quarters)
3) I then need to divide C1 "for example" by 13 for the quarterly calendar
period and print the results on each on the first 13 worksheet
4) this process is repeated for all the quarterly periods of the fiscal year.
5) lastly if the actual hours provided is greater than the planned hours I
need it displayed in red how do I get the hours displayed in red if the
manager uses more than her allotted hours?
I can do this process quite easily as numbers but using hours are quite
different. Please can you help!

If, as you say, you can do the process quite easily as numbers, then treat the
hours as numbers. There is nothing requiring you to enter hours in time format.
Just enter 52000 as a number, and proceed from there.

--
Regards,
Fred


"Jesops" wrote in message
...
I I have created a word report linking 9 Excel workbooks each using 52
worksheets (Annual) Each workbook also has a calculation worksheet. I have
been requested by my manager the following.
Service annual hours = 52000 hrs cell A1
1) I need cells B1,B2,B3 to split cell A1 equally for a the three districts
(annual)
2) Then each district say C1,C2,C3,C4 divide cell B1 (quarters)
3) I then need to divide C1 "for example" by 13 for the quarterly calendar
period and print the results on each on the first 13 worksheet
4) this process is repeated for all the quarterly periods of the fiscal year.
5) lastly if the actual hours provided is greater than the planned hours I
need it displayed in red how do I get the hours displayed in red if the
manager uses more than her allotted hours?
I can do this process quite easily as numbers but using hours are quite
different. Please can you help!

Hi Fred, I wish it were that simple!!
The 9 workbooks have 468 worksheets with 1404 tables all linked to a word
report. The divisional manager will enter in the annual planned hrs on the
calculation sheet, this in turn will automatically breakdown to quarterly
planned hours. Then again; breakdown too weekly planned hours on every
worksheet. When the area managers enter their actual hours and minutes worked
for every employee on the weekly time sheets they will be deducted from the
total planned hours leaving a plus or minus balance (hopefully in red for a
minus value) for that week; that in turn will be either added or deducted
from the next week. As you know Fred you can't set a sum calculating time
against decimals "or vies versa. so I need to be able to do the formulas in
time values. Any help would be gratefully accepted.

"Fred Smith" wrote:

If, as you say, you can do the process quite easily as numbers, then treat the
hours as numbers. There is nothing requiring you to enter hours in time format.
Just enter 52000 as a number, and proceed from there.

--
Regards,
Fred


"Jesops" wrote in message
...
I I have created a word report linking 9 Excel workbooks each using 52
worksheets (Annual) Each workbook also has a calculation worksheet. I have
been requested by my manager the following.
Service annual hours = 52000 hrs cell A1
1) I need cells B1,B2,B3 to split cell A1 equally for a the three districts
(annual)
2) Then each district say C1,C2,C3,C4 divide cell B1 (quarters)
3) I then need to divide C1 "for example" by 13 for the quarterly calendar
period and print the results on each on the first 13 worksheet
4) this process is repeated for all the quarterly periods of the fiscal year.
5) lastly if the actual hours provided is greater than the planned hours I
need it displayed in red how do I get the hours displayed in red if the
manager uses more than her allotted hours?
I can do this process quite easily as numbers but using hours are quite
different. Please can you help!

I remember reading on the newsgroups some time ago that the maximum
number of hours allowed to be displayed with a custom format of [h]:mm
is 10,000, so if you have 52000 then you will have to use a workaround
to get what you want to achieve. As Fred says, you can use numbers -
you can display these in a format that mimics time by making use of the
TEXT function (among other ways). Then when your subdivisions into
districts and quarters bring this below 10000, you could convert it to
time format for all the other sub-divisions.

Hope this helps.

Pete

Jesops wrote:

Hi Fred, I wish it were that simple!!
The 9 workbooks have 468 worksheets with 1404 tables all linked to a word
report. The divisional manager will enter in the annual planned hrs on the
calculation sheet, this in turn will automatically breakdown to quarterly
planned hours. Then again; breakdown too weekly planned hours on every
worksheet. When the area managers enter their actual hours and minutes worked
for every employee on the weekly time sheets they will be deducted from the
total planned hours leaving a plus or minus balance (hopefully in red for a
minus value) for that week; that in turn will be either added or deducted
from the next week. As you know Fred you can't set a sum calculating time
against decimals "or vies versa. so I need to be able to do the formulas in
time values. Any help would be gratefully accepted.

"Fred Smith" wrote:

If, as you say, you can do the process quite easily as numbers, then treat the
hours as numbers. There is nothing requiring you to enter hours in time format.
Just enter 52000 as a number, and proceed from there.

--
Regards,
Fred


"Jesops" wrote in message
...
I I have created a word report linking 9 Excel workbooks each using 52
worksheets (Annual) Each workbook also has a calculation worksheet. I have
been requested by my manager the following.
Service annual hours = 52000 hrs cell A1
1) I need cells B1,B2,B3 to split cell A1 equally for a the three districts
(annual)
2) Then each district say C1,C2,C3,C4 divide cell B1 (quarters)
3) I then need to divide C1 "for example" by 13 for the quarterly calendar
period and print the results on each on the first 13 worksheet
4) this process is repeated for all the quarterly periods of the fiscal year.
5) lastly if the actual hours provided is greater than the planned hours I
need it displayed in red how do I get the hours displayed in red if the
manager uses more than her allotted hours?
I can do this process quite easily as numbers but using hours are quite
different. Please can you help!

Regardless of how complex your workbook is, hours are as simple as numbers. You
just have to decide which format you want to use. I see two options:

1. Use Excel time format. This allows the user to enter data as hours and
minutes (hh:mm). You sum these numbers just like any numbers. To provide for
totals over 24 hours, use the format [hh]:mm. Even your 52,000 hours will be
displayed properly when entered as 52000:00 and formatted as [hh]:mm. If you
have a need to convert a time to a number of hours, multiply by 24.

2. Enter hours as a number. In this case, get your users to enter hours, and
parts thereof as a decimal number. Then do all your calculations as numbers. If
your users insist on entering times (ie, hh:mm), multiply by 24 before you do
any of your calculations.

Finally, I didn't know you can't sum times. I do it all the time.

--
Regards,
Fred


"Jesops" wrote in message
...
Hi Fred, I wish it were that simple!!
The 9 workbooks have 468 worksheets with 1404 tables all linked to a word
report. The divisional manager will enter in the annual planned hrs on the
calculation sheet, this in turn will automatically breakdown to quarterly
planned hours. Then again; breakdown too weekly planned hours on every
worksheet. When the area managers enter their actual hours and minutes worked
for every employee on the weekly time sheets they will be deducted from the
total planned hours leaving a plus or minus balance (hopefully in red for a
minus value) for that week; that in turn will be either added or deducted
from the next week. As you know Fred you can't set a sum calculating time
against decimals "or vies versa. so I need to be able to do the formulas in
time values. Any help would be gratefully accepted.

"Fred Smith" wrote:

If, as you say, you can do the process quite easily as numbers, then treat
the
hours as numbers. There is nothing requiring you to enter hours in time
format.
Just enter 52000 as a number, and proceed from there.

--
Regards,
Fred


"Jesops" wrote in message
...
I I have created a word report linking 9 Excel workbooks each using 52
worksheets (Annual) Each workbook also has a calculation worksheet. I have
been requested by my manager the following.
Service annual hours = 52000 hrs cell A1
1) I need cells B1,B2,B3 to split cell A1 equally for a the three districts
(annual)
2) Then each district say C1,C2,C3,C4 divide cell B1 (quarters)
3) I then need to divide C1 "for example" by 13 for the quarterly calendar
period and print the results on each on the first 13 worksheet
4) this process is repeated for all the quarterly periods of the fiscal
year.
5) lastly if the actual hours provided is greater than the planned hours I
need it displayed in red how do I get the hours displayed in red if the
manager uses more than her allotted hours?
I can do this process quite easily as numbers but using hours are quite
different. Please can you help!