It's very helpful but what about if i wanted to add more than one start and end date.. It means the staff will have two vacation in the same year? Thank u
Thank you for your feedback. If you want to add more than one start and end date then you will need to create additional columns where you will put respective dates. Let's assume you enter 2 new columns after E, so it will be Colum F and G, where you will put start and end date, then just add one more conditional formatting, copy it from already existing one, but just change the source of "Cell value" between =$c3 and =$d3, change it to =$f3 and =$g3. That's it, you will have the staff highlighted two vacation in one year. I hope it helps your query solved.
Thanks for your presentation. I would like to ask you, how can i make or update the record for the monthly summary of how many staff are expected to be going on leave and how many staff are currently on leave.
It's very helpful, thank you for this. I have a question. For those people who are already completed or finished their vacation how we can change the color?
Insert a helping column next to duration and use the formula =IF(AND("start_date" =TODAY()), 1,0) And then at the bottom just make the total for this particular column and that will be the total number of staff on leave on CURRENT DATE. From the formula please replace the word start date and end date with the cell reference where your start and end dates are.
It's very helpful thanks for this .if I want to calculate how many staff will be on leave in the month then what is the formula. If I can send max 10 employees at a time out of 86 then what is the formula what is
Brother i like your video but tell me 1 more thing that in the table its year for example 2022, if we need record of 2021 and 2020 in the same table so how should i do
Hi Joseph, Okay so for example you inserted a status column which happens to be column H, now select the entire area of the calendar, go to conditional formatting, new rules, use a formula to determine.... Then enter the formula (cell reference of your status colum for example is H4)) =$H4="On Duty" and then format it the color you want, and apply it. It's done. But make sure to move this rule on the bottom most, in rule manager. Also add one more forumla, just change the text to "Off Duty" hope you get you result.
Mohammad, thank you so much for the Planner! It is the simplest to make and the most efficient one! The only question - would it be possible when calculating vacation days to deduct from them public holidays?
Спасибо Большое. To deduct public holidays you can use formula NETWORKDAYS, =NETWORKDAYS(start_date, end_date) You can use cell reference of Start and End dates.
Slot leave and vacation leave are not necessary for one or more staff who were relieved of their services long long ago . Only the inbox is ...utilizes by every jack
Thank you for your feedback. If you want to add more than one start and end date then you will need to create additional columns where you will put respective dates. Let's assume you enter 2 new columns after E, so it will be Colum F and G, where you will put start and end date, then just add one more conditional formatting, copy it from already existing one, but just change the source of "Cell value" between =$c3 and =$d3, change it to =$f3 and =$g3. That's it, you will have the staff highlighted two vacation in one year. I hope it helps your query solved.
@@XLGuy What if i want to show the leave starting & end date in the color coding at start and . Ex : Jan 01st and Jan 15th should show in the chart so that i can hide the column C & D
Wow I've been looking for this kind of idea. Very helpful. Sir I have a question, how do you make it blank under the duration if 1 of the staff doesn't have an date entry?
![ละลาย (LALALYE) - DAOU PITTAYA ต้าห์อู๋ พิทยา [OFFICIAL MV]](http://i.ytimg.com/vi/wo6r9TgausI/mqdefault.jpg)
It's very helpful but what about if i wanted to add more than one start and end date..
It means the staff will have two vacation in the same year?
Thank u
Thank you for your feedback.
If you want to add more than one start and end date then you will need to create additional columns where you will put respective dates. Let's assume you enter 2 new columns after E, so it will be Colum F and G, where you will put start and end date, then just add one more conditional formatting, copy it from already existing one, but just change the source of "Cell value" between =$c3 and =$d3, change it to =$f3 and =$g3. That's it, you will have the staff highlighted two vacation in one year. I hope it helps your query solved.
Very helpful! Thank you so much
I really appreciate this. its so useful and very simple without macro.
Excellent presentation. Great attitude.
Thanks for your presentation. I would like to ask you, how can i make or update the record for the monthly summary of how many staff are expected to be going on leave and how many staff are currently on leave.
Nice information santu
It's very helpful, thank you for this. I have a question. For those people who are already completed or finished their vacation how we can change the color?
Very easy to build. Thank you. What if i want to remove weekends
Thank you it was very effective and beautiful also
this was very useful
What if one has to go on different dates.
Sir in the example, how can we also view total number of staff on leave on a particular day?
Insert a helping column next to duration and use the formula
=IF(AND("start_date" =TODAY()), 1,0)
And then at the bottom just make the total for this particular column and that will be the total number of staff on leave on CURRENT DATE.
From the formula please replace the word start date and end date with the cell reference where your start and end dates are.
It's very helpful thanks for this .if I want to calculate how many staff will be on leave in the month then what is the formula. If I can send max 10 employees at a time out of 86 then what is the formula what is
Same for me, me too want to see availablity percentage of employees
How to adjust leave period twice or thrice in a year ?
Please check the pinned comment.
Pls can this be done on google sheet?
Brother i like your video but tell me 1 more thing that in the table its year for example 2022, if we need record of 2021 and 2020 in the same table so how should i do
How can I use two different colour if i add a status coloum like on duty and off duty in the same vedio you have posted :-)
Hi Joseph,
Okay so for example you inserted a status column which happens to be column H, now select the entire area of the calendar, go to conditional formatting, new rules, use a formula to determine.... Then enter the formula (cell reference of your status colum for example is H4)) =$H4="On Duty" and then format it the color you want, and apply it. It's done. But make sure to move this rule on the bottom most, in rule manager. Also add one more forumla, just change the text to "Off Duty" hope you get you result.
Mohammad, thank you so much for the Planner! It is the simplest to make and the most efficient one! The only question - would it be possible when calculating vacation days to deduct from them public holidays?
Спасибо Большое.
To deduct public holidays you can use formula NETWORKDAYS, =NETWORKDAYS(start_date, end_date)
You can use cell reference of Start and End dates.
Slot leave and vacation leave are not necessary for one or more staff who were relieved of their services long long ago . Only the inbox is ...utilizes by every jack
i wanted to add more than one start and end date so tah my employee can plan well in advance =If(and(calendar date>= startdate,calendardate
Thank you for your feedback.
If you want to add more than one start and end date then you will need to create additional columns where you will put respective dates. Let's assume you enter 2 new columns after E, so it will be Colum F and G, where you will put start and end date, then just add one more conditional formatting, copy it from already existing one, but just change the source of "Cell value" between =$c3 and =$d3, change it to =$f3 and =$g3. That's it, you will have the staff highlighted two vacation in one year. I hope it helps your query solved.
@@XLGuy What if i want to show the leave starting & end date in the color coding at start and . Ex : Jan 01st and Jan 15th should show in the chart so that i can hide the column C & D
Hi
Same Employe going to more than one, how to add same.
Wow I've been looking for this kind of idea. Very helpful. Sir I have a question, how do you make it blank under the duration if 1 of the staff doesn't have an date entry?
If you attached download link for this excel, then it would be useful
very nice thank you so much can you please help me how to display the date on the bar highlighted
Actually it is not a bar, it's just set of cells that are formatted conditionally.
please explain for two time leave plan in one year one leave in january20 and 2nd leave in Mar20
Create new raws for start and end dates and then create new rule again as you did at first.
Conflict leave date in other colour plz
thank you
You're welcome 😊
Sir..can u share this excel sheet
What if employees 1 have go leave four times in a year
Hi Niranjan
Please read my reply to a comment from Umsarah.
Want that excel file
VERY NICE...PLEASE SEND ME EXCEL SHEET
Weekend shouldnt be counted