Hello Everyone,
I'm trying to create a schedule where it will automatically highlight the columns that fall on a time card due date. The due dates usually fall every 16th day and 31st day of the month unless those days fall on Saturdays, Sundays or Holidays. How would a create a Weekday Formula for this?
Your help is greatly appreciated.
as you seem to already have a list of dates on the LIST sheet you could use those to highlight using a count
=COUNTIF(LIST!$C:$C,B$4)>0
and also the count and IF for putting TC in cell
=IF(COUNTIF(LIST!$C:$C,Day!B4)>0,"TC","")
OR is it the list in column CC you need to automatically change
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Re: Conditional formatting of Due Date
Hi Etaf,
Thank you for the help, and for configuring the template. But what I was trying to figure out if there is a certain formula for those time card due dates that will calculate itself at a given year, month and day just like the different Holidays of the year. The Scheduled due dates are given to us yearly in the form of calendar, I’m trying to figure out how to derived to this without waiting for the scheduled calendar next year.
Anyways thank you my friend for helping.
you would need to keep a holiday list for your region, most change
The a lot of months only have 30days,
Do you then move the day to the nearest first or last
So if the 16th was a saturday or Sunday , do you then choose friday or monday
If a holiday next day or previous day , again if weekend Friday or of holiday Thursday or monday
Excel will need all the rules to be able to do this and a list of holidays for the year in question
Hi Etaf,
I uploaded the Calendar Timesheet, hopefully you would be able to figure this out. Looks like all the due dates (Supervisor’s Deadline) that fall either on Saturday or Sunday gets scheduled on Friday’s except the one on December where it falls on Monday. Honestly, I’m happy with the solution you gave me previouslyit’s just that every year I have to enter the new schedule.
Thank you....
OK, SO I have made a first draft
on LIST
column F calculates the !6th of Month and EOMonth based on the number (Year) BUT is just the Number 2020 , 2021 etc NOT a date
in cell K1
So that can be changed each year
So now we have the 16th & Eomonth Date for the year in column F
Column E just gives the DAY of that date
Column G then works out if a weekend, and moves the date to the previous Friday
Column H just gives the DAY of column G
Column M tests your dates in column C against the calculated dates in Column G
Now we need to test holidays and consider those - BUT i dont know all the holidays , but even looking at the calendar supplied I still dont see why the FALSE in column M is there see comments in column N
this maybe due to the square, employee deadline , so those dates would need to be added to a Holiday list
anyway , its a start
Hi Etaf,
This is really a tough one to figure out, I’m happy with the first solution. Thank you very much for your help.
ok, well at least it will generate the majority of the dates for you
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks