• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

excel logic help required

V

vpxlquest

Member
I work with excel 2007. I have an excel spreadsheet which requires some calculations. Calculating the course fee that the company will incur this year to run the refresher program. The dates are when the employees last did the course.


Eg.


participants course 1 course 2 course 3

emp1 11/10/2010 12/12/2011

emp2 1/1/2012

emp3 11/6/2010 12/12/2011


Given the above i need to calculate how much it would take if each employee took the refresher course. i.e the cost involved.


My thoughts are to group each employee in 2 categories.


Refresher - new to the course + employees whose course has expired need to take the refresher course.


Renewals - employees whose courses are due by say one year who need the refresher next year but their costs does not need to be listed this year.


Is there a more structured way of calculating this data. I can use pivot table and some conditional formatting any help will be appreciated.
 
There are some data missing:


Are your dates mm/dd/yy or dd/mm/yy?

How long is a course valid?


Once that is done, you just do date math to check the dates.


=DAY(TODAY()) will give today's day, which can be compared to the course's day.

=MONTH(TODAY()) will give today's month, which can be likewise compared.


Or you can take =TODAY()-365 to get a very good estimate of "one year ago."


Honestly, you can do this any way you'd want to do it. Write down your desires in English (or Spanish, if you're SirJB), and I can give you the formula.


HTH,

Don
 
My dates are mm/dd/yy. Course 1 validity is for 3 years , course 2 is for 2 years and course 3 is for 4 years. Also i first need to calculate individual participant validity if their course completion has expired or they are still valid till another year. each course has a different course fee. So if i get the count of number of participants in each course who are new and whose course certificate has expired than lets say for course 1 there are 5 in the above category than it is a matter of saying 5 x $1000 etc. i am not sure how to calculate if they are still valid for another year plus.


any pointers would be appreciated.
 
Hi vpxlquest,


I have worked out a file have a go through it:

http://dl.dropbox.com/u/60644346/Course_vpxlquest.xlsx


Logic Behind my workout:


- Every new emplyee has to take a course (mean you will incur cost for it)

- If the gap between last training and today is less then required period, cell will show "." for it.

- If the gap between last training and today is greater then required period but less then (required period + 1 Year), the person's status will be 'Renewal' and no cost will be inured for this time.

- If the gap between last training and today is greater then required period + 1 Year), the cell will show 'Refresher' and corresponding cost will be incurred.


About Threshold Days:


If a training is due after X years,

- Threshold for it will be 365 * X Years for 'Renewal'

- Threshold for it will be 365 * ( X + 1) Years for 'Refresher'


This i added just as 'Visual' aid for comparison...


Regards,

Faseeh
 
Thanks for taking the time to create this xl sheet. it is very useful and i can customize it for my requirements. I currently am using countif to count the number of employees signed up for a course. since the dates are listed as month/day/year i used some helper columns to derive the year(date) . using the extracted year i did countif to get the number of employees. is there a direct way to use countif(year(date)) function.

eg. 02/26/2012 year(date) will give 2012. how to say countif(year(02/26/2012)) to get the results.


thanks again for your inputs.
 
Hi vpxlquest,


Actually you can do that but with a trick. For that you need to understand the date system in excel. The dates in excel start from 1st January, 1900. This day is marked 01 or the first day, 2nd January, 1900 will be marked second day, and hence today i.e. 7th March, 2012 will be the 40975th day!! You can use this logic to check whether the date is within the year 2012 or not.


Lets assume that you want to check for the year 2012 and the sample date are as follow:


mm/dd/yy

12/2/2011

9/19/2012

9/5/2011

3/5/2012

5/10/2011

12/1/2011

3/19/2011

10/25/2012

3/4/2012

3/22/2011

5/1/2010

10/25/2010

12/15/2011


The First day of 2012 i.e. 1st January, 2012 will be the 40909th day for excel. you can use this formula to check for year 2012:


=SUMPRODUCT(((B3:B15)>40909)*((B3:B15)<41278))


....where dates are located in B3:B15. The result of this will be 04, as 04 date have 2012 as year and 40909 represents 1st January 2012 & 412078 represents 1st January 2013.


Hope this will help.


Regards,

Faseeh
 
You must log in or register to reply here.
Back
Top