Track expiry dates for employee's certificates.
Вставка
- Опубліковано 26 сер 2024
- A certificate tracker for your employees in Excel. Track expiry dates for your employee's certificates. Conditional format dates that are 60, 90 and more than 90 days away. This is designed for managers, safety managers and education departments of firms. To receive a copy of this file click www.easyexcela... and click on Free Templates
Are you struggling to update your Microsoft SQL Server data?
I recommend using well-known Excel to update and manage your data in SQL Server using the SQL Spreads Excel add-in.
Click sqlspreads.com... to find out how SQL Spreads works best for non-technical end users and download our free 14-day trial. You will get two months free when purchasing an annual subscription using our link.
Check out my online training www.easyexcelanswers.com/courses.html
For more help visit my website www.easyexcelan... or email me at easyexcelanswers@gmail.com.
Contact me regarding customizing this template for your needs.
I am able to provide online help on your computer at a reasonable rate.
Check out Crowdcast for creating your webinars
app.linkmink.c...
If you need to buy Office 2019 follow
amzn.to/2VX5dv8
Follow me on Facebook
/ easyexcel.answers
Follow me on twitter
easyexcelanswers
IG @barbhendersonconsulting
You can help and generate a translation to you own language
www.youtube.com...
*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I've used or have experience with.
![АЛАУДИНОВ у Скабеевой: эти их ВСУ нас НЕ ДОГОНЯТ 😁 [Пародия]](http://i.ytimg.com/vi/an0anqU4WGQ/mqdefault.jpg)
Great work Dear, Thanx for video.
5 years later and you're still helping people Miss Barb. Thank you
Thank You!
Thanks for the video. It's really helpful. Just wondering how to set up a email notification for the expiring documents.
I have the exact same question!!
Same here, I've been searching for a video tutorial. If anyone finds anything please let me know.
My life will be easier if this process sent an automated email whenever the set date for a reminder is triggered
This is exactly what I am looking for! Thank you so much for posting and I am subscribing!
Welcome aboard!
The health and safety training certificates validity are usually 12 months, 24 , and 36 months and not less than a year.
The point of the video is to show you the process!
Thank you for this video! My director asked me today if this feature is possible in Excel. So, let me get busy, and thank you for posting your extremely helpful video!!
email me at easyexcelanswers@gmail.com and I will send you a copy and half of the work will be done!
Hi Barb. I know nothing about xl but need to set up a table like this to keep track of training dates for staff who do 3 monthly refreshers. How do I even begin to to set this up. The majority of staff will have different dates of when they done the training and when their Refresher is due.
email me at easyexcelanswers@gmail.com and I will send you the template
@@BarbHendersonconsulting is it okay if i email you as well? i am an absolute novice and I need a spreadsheet like the one you demo'd up here. Majority of staff in my case also have different dates. Please and thank you
I made this sheet and it works great, but what is the formula for making the cells with no info in them stay white? as I just made a table with all formulas
you surround the formulas with =ifna(formula,"")
Hi Barbara-Thanks for this video. I have sent an email to request further instructions🤩. Thank you
Got it!
Thanks so much for your assistance on this topic Barb. You went above and beyond to help out my unique issues on this topic. I have happily subscribed to your channels and I will be sending friends, and colleague's your way.
You are so welcome!
THANK YOU THAT WAS GREAT EXPLAINED. YOU ARE AN AWSOM TEACHER
You're very welcome!
Thank you very much, exactly what I was looking for without any bulky software. Cheers!
Great to hear!
This is perfect!!! Thank you so very much! I really appreciate your video!!!
You are so welcome!
Thanks for the help, was just wondering if you could send this template to me?
Sent!
Good evening, I came across this very instructional video for a project that I want to do, which is tracking expiration dates of student CPR/AED/1st Aid certifications. This looks like it will fit the bill, however, as Augustine Benny asked 1 year ago, is there a way to get email notifications of expiring documents in any of the 'time periods' (30 days, 60 days, etc)?
The only difficulty with that would be making sure that the file was open every day if date =certdate +30 then send email. You could not use less than because they would receive a message everyday.
I want to specify the range date foe nearby due date alert color if possible since green and red work fine but yellow for nearby due date range doesn’t work with me. I would like to communicate with you by video call after your agreement. Thank you for amazing work.
calendly.com/easyexcelanswers
I try to do this, after applying the formatting conditions & it's not coming as you said. Can I pls help me to clear it out.
email me at easyexcelanswers@gmail.com and I will send you a copy
If I wanted to make it to where expired ones were Red instead, how could I do that?
Change the date criterial to greater than expire date and turn it red
Wow thx been struggling with this
glad I could help!
Hi Barb, good content and i do learn from the video. I have a request aka help.. i manage medicine at my place, and it is very troublesome for me to track medicine that is almost expired within 6 months without complete stock check.. i do work alone at my place thus i have time issues in solving everything.. my question is, is there any function to help me in tracking this thus making a list automatically at another sheet or tab of near expired items starting from 6 months?
Thanks for any attention given..
There is nothing I know of for this but if you contact me this is something I could develop for you easyexcelanswers@gmail.com
@@BarbHendersonconsulting i will send an email for the help request..thanks for your response
Muito Obrigado Barb, Gostei muito do video
de nada
Thank you for this video. It has helped greatly, however, I am having one issue today date being 28/10/2022. For some reason any date in january 2023 is not formating. would you know what the issue might be?
dates are formatted in the American format, mm/dd/year
Just came across this video and is very helpful but the problem I am having all the cells are same colour cant figure out where im wrong
email me at easyexcelanswers@gmail.com and I will send you a copy
Hi, I have a similar data but want to display this in pivot table (non-expired data shouldn't be visible), can we achieve this task in pivot tables for expired dates & nearing expiry dates only.
I Have never tried it in a pivot table
Thank you for the video. this is what I've been looking for. I do have a problem when coloring the cells. when I go to the manage rules, there is no rules.... the box is blank and I can't add rules in.
I just made a video showing how to create conditional formatting ua-cam.com/video/6Zjs2NqZTJQ/v-deo.html Using high light cell rules will not work
@@BarbHendersonconsulting THANK YOU FOR YOUR HELP AND QUICK RESPONSE
@@atalieslone2679 your very welcome
Great work Thanks
happy tp help
Thank you, this was a great video. It really helped me! :)
Glad it helped!
If I were to add another line, what do I write in the format settings? I tried but it didn’t quite work. I want to have 30, 60 and 90 days.
Barb, is there a way to generate an email when the expiration date is approaching?
it could be done with vba code
Very good Henderson, one question, how can you make a report from each employee name with their expired certification name and expired date or nearly to expire about 30 days.
Thank you, the only way that I think you could do that is with a VBA macro.
Just what I was after for all the crew's certs. Is there a way to copy and paste the conditional formatting, so I dont have to enter for each date in manually. I have tried format painter and special paste (format). These don't pickup the same today() cells each time.
email me and I will send you a copy easyexcelanswers@gmail.com
This is wonderful, thanks
Glad you like it!
Hi there, It has been very helpful but for some reason I can't get the yellow 90 days to work for me. If you could please help - I have followed the video but it just goes white (using a MAC)
I cannot reproduce this because I do not have a MAC. If you email me at easyexcelanswers@gmail.com, I can send you my copy and you can see if that works
@@BarbHendersonconsulting Thanks I have sent the email
Did you format each cell separately? or is there a way to format them in one go, please ?
You are able to select the range of cells and format them as a group
Hello Barb,
I know only basics of Excel. I really want to learn step by step process of creating exactly same file. Please, do it for me if you can. That would be helpful for me and I guess there are lot of others who are exactly like me.
Thank you in advance.
here are two other videos that show basics
conditional formatting with a formula
ua-cam.com/video/IuMCGC6t5Lg/v-deo.html
Conditional format dates by age relative to today in Excel
ua-cam.com/video/Pnq8QLrFFyk/v-deo.html
@@BarbHendersonconsulting Thank you very much Barb. This will help me.
Good morning kindly assist with this template Barbara
contact me calendly.com/easyexcelanswers
been looking for this video for days now, thanks for this, that's exactly what i needed. can i just ask, is it possible to exactly the same but using the data bars in those cells with the dates instead of fill colours of the cells?
the colour makes it easy to read
Hi Barb, thanks for this, so helpful - how do i expand the conditional formatting through to all the rest of the cells? i have inserted the cell range under "applies to" but those cells' colour does not change for some reason? Screenshot attached
$B$5:$C$19
change $C$19 to reflect the cells required
Is there any way you could then link ONLY the expired certificates to show up in a sort of 'summary' worksheet? i.e. your "Sheet2".
You could make a list of all the certificates and their expiry. Then use the formula that I explain in my most recent video
ua-cam.com/video/77li3mP4PNg/v-deo.html, you would just get a list of the expired certificates
I have currently 70+ (still hiring) employees with certs to track. I would love a copy of this template to use! How may I request one? 🙏🏽😁
email me at easyexcelanswers@gmail.com
Awesome video, but what if i have multiple specific dates instead of today. How do i go about it?
You have multiple dates based on today, I do not know how you would do it another way
Sent you an e-mail. I cannot see the formulas well due to low resolution. Would you happen to still have this spreadsheet to share?
I have sent it to you
@@BarbHendersonconsulting You are so kind! Thank you so very much. This will really come in handy for us.
Good Day thanks for the video but I am struggling to get the 90 to show yellow, if I type the formula get error message from excel
Make sure that the b5 in =and(b5>$A$2,b5
@@BarbHendersonconsulting Thanks enstead of the comma I put a(;) a x now it is sorted
How do I get a copy of this excel spreadsheet?
just sent it
How do I make the cells that are blank (have no information in them yet) remain white
If ActiveSheet.Cells(r, c).Value = "y" Then
ActiveSheet.Cells(r, c.Interior.ColorIndex = 2
End if
@@BarbHendersonconsulting Thank you for the quick reply, Where exactly would I put this formula ?
@@mesomaejeh4416 Sorry, I gave you a VBA answer, What you need to do is select all the cells, on the home tab -Conditional formatting -Select cell equal to and when the dialogue box comes up enter "" in the first box and select custom format and select white fill.
Thanks
Welcome
When I apply the formula to the whole data everything turns into 1 color ? What should I do ?
email me and I will send you a copy of the sheet. easyexcelanswers@gmail.com
@@BarbHendersonconsulting just emailed you. Thanks
Is there any way I can get a copy of this to use for tracking my guys certifications? I run a 5 guy tower climbing crew and this would be excellent for tracking their safety and training certifications, as well as give me heads up as to when they need training review or re certification.
email me and I will send you a copy easyexcelanswers@gmail.com
@@BarbHendersonconsulting Thanks for getting back to me so quickly. I actually was able to make it myself by following suit. Was being lazy at first. Sorry about that, and thank you for the great video.
@@BarbHendersonconsulting Ironically enough I manage a tower climbing company and am going through the same process. I created the spreadsheet with the dates but I can't figure out how to add the rule. Do i have to manually create each 3 rules in every single cell? Please let me know or if you could email me a copy that would be amazing. Thank you.
@@annabellemead122 I have sent you a copy
Hello Mrs. Barb, thanks so much for the video. I emailed you. Will you share the template with me?
Sent!
How do you make this work with employees with multi different expiration dates?
That is what the different columns next to the name are for. You have a number of certificates and different expiry dates relative to today's date
@@BarbHendersonconsulting when the next day today’s date changes how does it affect data
Hi barb thank you for this video I just sent you an email in hopes I can get a copy of this spreadsheet THANK YOU!
Sent!
Hi Barb,
I had sent you an email yesterday if you can please help.
Thanks
Sent!
Lmao this is why i left corp.
that is funny!