Forum Discussion
Excel Yearly calender conditional formatting
Select the cells that you want to format conditionally.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Lave the first drop down set to 'Cell Value'.
Select 'equal to' from the second drop down.
Enter ="Sat" in the box next to it.
Click Format...
Activate the Fill tab.
Select green.
Click OK, then click OK again.
Repeat the above steps, but with ="Sun" instead of ="Sat".
I like to help other users because I like a mental challenge - it keeps me sharp. Forums like this one helped me enormously when I still worked (I have been retired for 5 years), and it's nice to be able to give back.
HansVogelaarAah have a nice retirement! I am doing an internship and this is a side project for a family member but i also use forums like this on Power BI for my internship and they help alot! People like u are amazing. My vacation for schools is done in the excel sheet. I should be able to rework the exact same data for my other needed info. Can u maybe share me a way how i could put all formulas on 1 other sheet? Input data like vacations will still be on the sheet of calender but like the big formulas would be on a sepperent hidden sheet so that the use is userfriendly and cant break easily
.
Solved via PM.
- I haved pmed you the file of data.
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.
- My bad i am rambling.
So im trying to automatticly fill the sheet with hours of the week. Every week has the same hours.
However when there is a vacation i'd want this formula to output nothing. Empty cell or maybe like a "vrij" in it or something.
I also want the input data i have to not switch days. Now when i switch from year since i made it the same as my calender it switches days meaning Tuesday would be first on the list instead of monday. Yet the hours dont change with so u'd need to manually change the hours for the weekdays. I want this automated or the days to just not move. Which data would u need to try and help me? I'm sorry, I don't understand your description, nor your question.
Alright thank you i think i'd preferr to just tell her to copy from another week with same day etc.
Hopefully last question about this sheet.
I got a weekly hours fillin form but i want when i change years that this automatticly changes with.
2024:2025:
The days already go with. The best wished outcome:
Mon: ActualHours
Tue: Actualhours etc.
Mo-Su.
So if possible i'd like the days to rotate but not switch actual cell data.
the hours get put into the sheet doing:
=AO5, AO6, etc. The yellow filled cells being AO5-11.
Any good way to do this?
Edit: Found another thing i want to force.
When custom formatting is enabled(For vacations etc) i wanna make that cell be empty but have the formula there somehow. I dont think this will be possible without code which would be sad. If thats the case let me know any data u'd need to help me out writing that code if u got time haha.The only way to restore the formula automatically would be using VBA code. But that would require all users to allow macros when they open the workbook.
- That is sad news. Do u know any work arounds to input the formula's data? If not i will have to explain to the user how to put back the formula after which should also be no problem
That's not possible - if the user enters a value in a cell, the formula in that cell is overwritten and cannot be retrieved...
Hey! I have got a question for you. I know how to do what i want but i kinda want to make it cleaner then how i am about to do it.I wanna be able to automatticly fill the entire calender empty spots depending on a dataset we manually put it. Almost like the vacations.HansVogelaar
I think its easy to understand what i intend to do it.
I could just do =$AO$5 for every monday etc for the week. However there should be a cleaner way to do this. Do u know it? I also need the user to be able to edit 1 monday from the year yet the formula be unaffected. So if they manually entered a number in the calender it would replace formula until the cell is empty.- Wait i realise what i've done. Fixed it. Thought vakantie gastouder wasn't the sheet but just a ALTText bassicly. I thought it had to do to cell calling but it was sheet instead. Got it! Now just need to apply this trick one more time and i should have all the things i need. U have been amazing.
That suggests that the name of the sheet is spelled incorrectly. Could there be a space before or after the name in the sheet tab, for example 'Vakantie gastouder ' ?
So uhm, I thought i had it. I want to do the exact same thing as school vakanties again. This time for gastouder vacation. I made the list with vacations in it. Edited your formula from:
=SUMPRODUCT(('School vakanties'!$F$6:$F$26<=DATEVALUE(Sheet1!$D5&"-"&OFFSET(Sheet1!E$4,0,-ISEVEN(COLUMN(Sheet1!E5)))&"-"&ActualYear))*('School vakanties'!$G$6:$G$26>=DATEVALUE(Sheet1!$D5&"-"&OFFSET(Sheet1!E$4,0,-ISEVEN(COLUMN(Sheet1!E5)))&"-"&ActualYear)))to:
=SUMPRODUCT(('Vakantie gastouder'!$J$6:$J$26<=DATEVALUE(Sheet1!$D5&"-"&OFFSET(Sheet1!E$4,0,-ISEVEN(COLUMN(Sheet1!E5)))&"-"&ActualYear))*('Vakantie gastouder'!$K$6:$K$26>=DATEVALUE(Sheet1!$D5&"-"&OFFSET(Sheet1!E$4,0,-ISEVEN(COLUMN(Sheet1!E5)))&"-"&ActualYear)))
I added the conditional formatting and all. It askes me to insert a file when i fill the formula.- Thank you! Will do this when i am at the work place.
That is actually very easy - you don't have to modify the formulas yourself.
- Create a new worksheet.
- Switch back to the calendar sheet.
- Select the range with the auxiliary formulas (BE5:CB35 in my sample workbook).
- Press Ctrl+X to cut the range to the clipboard.
- Activate the new sheet and select a target cell - E5 would be a good choice.
- Press Ctrl+V to paste the range.
- Excel will fully automatically update the formulas to refer to the new location of the formula range.
- If you wish, you can now hide the new worksheet.