• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic whole year Calendar

Hi Sajan,

It worked very well your date logic leveraged! :)

Now if I want to highlight Holidays.. which way is easier to work with?
I tried to create a list in the Inputs tab but it didnt work
I tried to add a formula then use CF to format the cells... but maybe I didnt incorporate well the formulas together? Because I already a date "formula" in cells.. and now adding another formula, I got an error..
Any suggestion?

Thanks,

Krysta
 
Hi Krysta,
you can use something simple like
=ISNUMBER(MATCH(A1,Holidays,0))
Replace A1 with the actual cell that you are interested in highlighting.

Cheers
Sajan.
 
Hi Sajan,

Tried that and it didn't work..
I tried something simpler to see if it worked and no better luck. I tried to add this formula in the CF
=COUNTIF($A$15:$A$24,B$6)>1

Here is my doc with the formula added maybe you can tell me what I did wrong? Either with my formula or yours?

Thanks
 

Attachments

  • Krysta_Calendar.xlsx
    46.6 KB · Views: 6
Hi Krysta,
Select the range of cells you want to highlight for holidays, and apply the following Conditional Format formula:
=ISNUMBER(MATCH(B$6,Holidays,0))

For example, if you select the range B6:NB13 and apply the above conditional format rule, it will highlight all of your holidays.

You could also use your formula with a slight modification:
=COUNTIF(Holidays,B$6)>0

Cheers,
Sajan.
 
Good morning Sajan,

Stupid me.. your formula worked at the first time...
I copy paste your formula =ISNUMBER(MATCH(B$6,Holidays,0)) again and it didn't ''work'' again at first... but then I realized that I didnt select ALL the range.. o_O
Sorry for wasting your time. :p
But thank ou for taking the time to get back to me with the correction of my formula..

See you,

Krysta
 
Good morning Krysta. No worries... sometimes it helps to step back from the details, and to see it through another pair of eyes!

-Sajan.
 
Hey Sajan,

here goes another question:
I would like to highlight the payday for certain person let say. I would like to CF every second Friday.
So I tried this:
=IFMOD(($L$7-$B$7),14)=0
where L7 is the first payday...

That formula doesn't seem to do anything. Can you magically fix my formula please?
I attached the doc with the formula....

Gracias

Krysta
 

Attachments

  • Krysta_Calendar.xlsx
    46.5 KB · Views: 10
Hi Krysta,
Before I write out a formula, I find it helpful to write down how I would solve a problem manually. That makes it easier to convert the logic into a formula.

To highlight every second Friday, how would you solve that problem manually? And remember, for Excel to "obey your commands", you have to explicitly describe each step required.

Once you determine your manual approach, I can help you convert that into a formula.

Cheers,
-Sajan.
 
@Sajan
Hi!
I guessed so, since it works with my girl friend's dog. But not always, i.e. with =LEFT(<that_same_guess>;LEN(<that_same_guess>)-6) it doesn't work at all. I got barked instead.
Regards!
 
hihihi, I tried a few of those commands and it's working half and half..

Manually, I would:
From a calendar that starts on Wednesday, January 1, 2014, lets say that my first Friday Payday is January 3rd.
I would select Fri Jan 3rd then I would select Jan 17 which is 14 day later and so on. Every 14 days, I would select then click on Home --> Fill Color--> select a color.

So with those manual steps, at first, I tried simply in my CF formula D7 (first payday- Jan 3rd) + 14
=D7+14
It didn't work.

I saw on the internet that another person suggested =IF(MOD(($B4-C$1),14)=0,"X","") where B4 is contains the date of the first payday and C1 contains date... then Conditional Formatting -> Highlight Cell Rules -> Text That Contains -> Set text to X and pick color
That worked as I tried in a simple calendar without other furmula added! BUT not in my calendar.. Because in my cells, I already have a formula (i.e =D$6), when I tried to integrate this second formula, maybe I din't put them together correctly, but it didn't work.

Advice?

Thanks,
 
Hi Krysta,
A couple of questions...
Is the payday every other Friday, starting with the first Friday in a year, or is it the 1st and 3rd Friday in each month, or will it always be determined based on a Friday that you specify, and counting 14 days from that day onwards?

-Sajan.
 
I just saw the second part of your question... you can add additional Condition Format rules by clicking on the "New Rule" option. (Similarly, you can edit the existing ones, reorder the application of the rules, etc. from the "Manage Rules" screen.)
 
Hey there Sajan,

It will always be determined based on a Friday that you specify, and counting 14 days from that day onwards :)

(I thought I posted my reply this morning.. but it look like not.. weird)

Thanks
 
Hi Krysta,
You were on the right path with your formula, with subtracting a date from the firstpaydate. You just needed to tweak your cell references a bit.

Setup a Named cell reference called FirstPayDate, and input the date of the first pay date in the year (or for that matter, any paydate in the year before your calendar in B6 starts.)

Then select the range of cells to highlight (e.g. B6:NB13) and put the following Conditional Format rule:
=MOD(B$6-FirstPayDate,14)=0

Cheers,
Sajan.
 
Good morning Sajan!

Brilliant! It's working like a charm! :)

Thank you very much for the help, as always!

Krysta
 
You must log in or register to reply here.
Back
Top