Calendar Function

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Is there are way to quickly determine which dates of the month are workdays
and weekends and change the cell background color accordingly?

For instance, if I were to type in a series of e.g. 14 dates for a specific
month/year (please see below in ****s), then the weekends should
automatically be highlighted let's say in "gray".

********
4-1 4-2 4-3 4-4 4-5 4-6 4-7 4-8 4-9 4-10 4-11 4-12 4-13 4-14
4-15

********

In this example (range A1:O1), I want the 4-3 & 4-4, 4-10 & 4-11, 4-17 &
4-18, 4-24 & 4-25 (all weekends) be highlighted in gray.

However, once the data of cell A1 is changed from 4-1 to 5-1, I quickly
would like all cells to be adjusted (not only the dates themselves but also
the color formatting of the cell background).

Any suggestions?
 
Hi
have a look at the WEEKDAY function and use this in the conditional
format dialog. e.g. the following formula in the conditional format
dialog would color cell A1 if it's a sunday
=WEEKDAY(A1)=1
 
Frank:

Thanks! I now realize that I need to get a bit more sophisticated. I hope
you'll be willing to provide me more information.

Here's what I have in a spreadsheet - the cell references are static:

1. Cell B9 - contains date of either "mid-month" or "last-day-of-month).
This date is manually entered.

2. Cell Range B12:P12 - contains numbers (text format) referencing dates
(from 1 to 15)

3. Cell Range B13:Q13 - contains numbers (text format) referencing dates
(from 16 to 31)


Here's what I'm trying to achieve:
- "Read" the value of B9
- If B9 is "mid-month" (e.g. 1-15, 2-15), then "find 4 weekends" in cell
range B12:P12 and highlight the cells that represent a weekend of the (more
than likely) 8 cells
- If B9 is "end-month" (e.g. 1-31, 2-28), then "find 4 weekends" in cell
range B13:Q13 and highlight the cells that represent a weekend of the (more
than likely) 8 cells


So, essentially, by typing in the appropriate date in B9, the "4 weekends"
in either row 12 or row 13 should be highlighted.

Do you any suggestions if this is even possible?
 
Hi
not quite sure what you mena with numbers AND text format?. What
numbers are in B12:P12.

I would make my life easier to put real dates in this range. e.g.
- in B9 enter a real date
- in B12 enter the following formula
=DATE(YEAR(B9),MONTH(B9),1)
- in C12 enter
=B12+1
- in B13 enter
=DATE(YEAR(B9),MONTH(B9),16)
- in C13 enter
=B13+1

now select the range B12:Q13 and goto the conditional format dialog.
Enter the following formula to color weekends:
=(WEEKDAY(B12,3)>=5)*($Q12>=$B$9)
- choose a format
 
Frank:

I set the spreadsheet up the way you recommended (at least I believe I did).

There is one issue that I haven't been able to resolve entirely.

The weekends are "highlighted" properly in row 13 if B9 contains the
"last-month" date (of any month).

However, if B9 is the "mid-month" date (any month as well), no "weekend" get
highlighted in row 12. Instead the weekends of the "last month" are
highlighted in row 13 as well.

Any idea as to how the conditional formatting function should be changed to
accomodate that.

Thanks,
Tom
 
Frank:

- B9 stores a date
- Q12 is empty (values are "1", "2", "3", .... "15"; range B12:P12
- Q12 contains "31" (values are "16", "17", "18", .... "31"; range B13:Q13

In other words, range B12:P12 covers 15 dates (1st of month to mid-month)
while
range B13:Q13 covers 16 dates (16th of month to end-month).

Based on your asking what cell Q12 contains, I put a dummy value into the
cell. Although, the cell in row 12 are now highlighted, the "wrong
weekends" are highlighted.

Any idea how I could overcome this "empty cell" dilemma?

Thanks,
Tom
 
Hi Tom
just change the formula
=(WEEKDAY(B12,3)>=5)*($Q12>=$B$9)

to
=(WEEKDAY(B12,3)>=5)*($P12>=$B$9)
 
Frank:

Thanks... I'm one step ahead... but it's still not working as intended.

Based on the 15 values and row 12 and 16 values in row 13, I have applied 2
conditional formatting functions.

=(WEEKDAY(B12,3)>=5)*($P12>=$B$9) - if yes then "yellow cell color"
=(WEEKDAY(B13,3)>=5)*($Q13>=$B$9) - if yes then "yellow cell color"

Again, cell B9 contains the date.

OUTCOME:
# 1. If the date is <=15 then all weekends (row 12 and 13 are highlighted)
# 2. If the date is >15 and <= 31 then only weekends in row 13 are
highlighted.


PROBLEM:
Just like #2 (highlight weekends only in row 13), #1 should highlight
weekends only in row 12 (instead of both row 12 and 13).


I hope you don't mind me asking you for additional info in this matter.

Thanks again,
Tom
 
Hi Tom
if you like email me your file and I'll have a look at it
email: frank[dot]kabel[at]freenet[dot]de
 
Frank:

'just emailed you the file.

Thanks,
Tom


Frank Kabel said:
Hi Tom
if you like email me your file and I'll have a look at it
email: frank[dot]kabel[at]freenet[dot]de

--
Regards
Frank Kabel
Frankfurt, Germany

Tom said:
Frank:

Thanks... I'm one step ahead... but it's still not working as intended.

Based on the 15 values and row 12 and 16 values in row 13, I have applied 2
conditional formatting functions.

=(WEEKDAY(B12,3)>=5)*($P12>=$B$9) - if yes then "yellow cell color"
=(WEEKDAY(B13,3)>=5)*($Q13>=$B$9) - if yes then "yellow cell color"

Again, cell B9 contains the date.

OUTCOME:
# 1. If the date is <=15 then all weekends (row 12 and 13 are highlighted)
# 2. If the date is >15 and <= 31 then only weekends in row 13 are
highlighted.


PROBLEM:
Just like #2 (highlight weekends only in row 13), #1 should highlight
weekends only in row 12 (instead of both row 12 and 13).


I hope you don't mind me asking you for additional info in this matter.

Thanks again,
Tom
Click to expand...
Click to expand...
 
Back
Top