Create an Overdue Payment Reminder Letter Word for Inventory Effortlessly

Overdue payment reminder letter word for Inventory

in this video we will discuss about an Excel template which will give alert messages on expiry dates of policies services or documents which needs to be renewed after a certain period of time here is the list of my insurance policies Ott Services which I have subscribed and documents related to my vehicles in the second column we have the date of purchase this third column of the table contains the validity of services in months using this data I have calculated the expiry dates then the days left for expiry or days elapsed after expiry of this policies or services in the last Quorum we have this text expert against all expert services and experience soon against the services that are going to expire under seven days and also the rows containing expert services will be highlighted in red color and services that are going to experience next 7 days will be highlighted in yellow color now let's see how to create an Excel template like this same data which we saw a moment earlier here is the date on which I purchased my medical insurance policy 20th of September 2021 and the policy is valid for 12 months now to find the expiry date of this policy equal a date function open parenthesis first argument of a date function is start date here we have to specify the date on which the policy was purchased comma second argument months the policy is valid for 12 months close parenthesis minus 1 enter and here we have the expiry date of this policy 19th of September 2022. but today's 27th of September 2022 so that means it has been a few days since the policy expired so let's calculate how many days have elapsed after the expiry for that equal specify the expiry date minus today function open parenthesis close parenthesis and the cell is displaying a series of hash signs and this is because the formula returned a negative date will apply General format to the cell right click format General okay and we have negative 8 here means it has been 8 days since the expiry date of the policy here is the date on which I got the POC certificate of my car to calculate the expiry date of this certificate and taste left for Xperia or days elapsed after expiry I will copy this formulas into the cells below see the puc certificate will expire on 7th of October 2022 and there are 10 days left for expiry once again I will copy the formulas in the cells below see the date on which I purchased the insurance policy of my bike 15th October 2021 this policy will expire on 14th of October 2022 and there are 17 days left for expiry as we have used today function in the formulas whenever we open this file these values will update ing to the current date these cells will be displaying the number of days left for expiry or elapsed after expiry on that particular day next we need to display the text expert against the policies or services that have expired and expiring soon against the pulses that are going to expire under seven days for that we will be using nested if equal IF function open balances first argument of a function is logical test a negative value in this cell means the policy has already expired to check that we'll select this cell Less Than Zero if the value in this cell is less than zero that means the policy has already expired in that case this cell has to display expired comma double quotes expand tablet codes comma now we have to check whether the value in this cell is less than or equal to 7 for that if function open parenthesis less than or equal to 7 comma if the value in this cell is greater than zero and less than or equal to 7 we will display expiring Zone double quotes expiring Zone double quotes comma double quotes double quotes close parenthesis one second close parenthesis and right now the value in this cell is negative 8 means it has been 8 days since expiry so the status is expired let's modify the purchase date here we'll make it October nothing shown in the status that means there are so many days left for Renewal let's modify the date once again say I changed the purchase date to 29th of September 2021 and the policy will experiment 28th of September 2022. that means there is only one day left for expiry and the status is expiring soon we'll copy this formula into the cells below now to highlight the rose for expert policies and services in red color we'll select the entire table in the Home tab condition formatting new rule use a formula to determine which cells to format click inside this input box equal we have to select the first cell in the column containing status remove the dollar sign before the raw index 4. equal double quotes expired double quotes to select the color format fill for light red more colors okay okay once again okay see the expert services are highlighted in light red color to highlight the services which will expire under 7 Days in yellow color select the cells containing data conditional formatting new rule equal select the first cell in the column for status remove the dollar sign before the row index of the cell reference double quotes expiring soon double quotes format we'll select light yellow color okay once again okay [Music] [Music]