Some questions related to dates. 1

[marcq]

Hello.
Lets assume we have 7 days week and monday to friday are working days.
Now. Let's create 7 columns. For each column I will list workers that will do some job.
But what I want to achieve some autmation -
1# calculate that first column will be let's say always Monday.
May I use date today to fill seven columns autmatically (calculated)?
2# And the second question.
How to display holidays in this 7 columns based on earlier created list of dates?. I mean. For example I can do list of dates in separate hidden column - but then I want to have it in red in column if day is within observed week.

 

[SkipVought]

hi,

May I use date today to fill seven columns autmatically (calculated)?

Yes.

Make a list of your holidays observed. You can use the Conditional Formatting feature, for instance, to highlight such dates.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[marcq]

Hi SkipVought
I will test this feature.
BTW. Maybe you can tell me if this is possible to do:
Assuming we have:
Cell where we put hours of work
[WH]
Cell with Today().
[Today]
Then another cells (please mind also dates are put in cells:
[MO|TU|WE|TH|FR]
[ | | | | ]
Lets assume is Monday today. I put 40h to cell [WH].
[WH]->[40]

I want to have:
[MO|TU|WE|TH|FR]
[40|40|40|40|40]
So I put that each cell is = [WH]
But.
We open sheet on wednesday. I change WH to 20 because of dynamic change. I'd like to have change like that:
[MO|TU|WE|TH|FR]
[40|40|20|20|20]
Let's open again on Thursday and we have update to 5. I want to again put [WH] = [5]. I'd like it autmatically have calculated

[MO|TU|WE|TH|FR]
[40|40|20|5|5]
Is it possible at all?

 

[SkipVought]

New questions ought to be posted in new threads.

You can do this with a formula, with a circular reference, but I do not recommend, like...
[tt]
12-Dec 13-Dec 14-Dec 15-Dec 16-Dec
Mon Tue Wed Thu Fri
[highlight]40[/highlight] 40 20 20 20
[/tt]
where the formula for [highlight]this cell in C3[/highlight] is
[tt]
C3: =IF(C$1>=Current,WeekdayHours,C3)
[/tt]
where Current is today's date and WeekdayHours is your WH.

I would do it with a small macro, triggered on the Worksheet_Change event of the WH value.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]