Prorated dates.

[CKMax]

Where we work, we screen applications and give credit, prorated (i.e. full credit for full time job, 1/2 credit for part time job) for prior employments. Is there a way to to do the following?

Find the years, months and days between two periods (got this one).

Calculate how many years, months and days this period would be if employee did not work full time.

Finally, lump this periods of service into one, big, single period.

I can always add years and months, but with days, I cannot do that (not all months have 30 days). Thanks a lot for your help and let me know if more info is needed.

Thanks.

 

[plettieri]

Hello CKMax
Welcome to the Board

It might be more helpful to show some of the spreadsheet data that you are working with and the expected outcome. You can use the
Download Colo's cool utility for displaying your Excel Worksheet on the board for this purpose.

Dates/times have to be tweeked a little sometimes to get the desired result...

pll
This message was edited by plettieri on 2002-10-20 22:02

 

[maxflia10]

I'm not sure if this will answer any of your questions but check this thread, especially the formula by Larry (Indiantrix), or for that matter all of formulas.

http://216.92.17.166/board/viewtopic.php?topic=25910&forum=2

 

[Yogi Anand]

Hi CKMax:

Welcome to the Board!

I have used some sample data, and an equivalence factor of 0.5 for parttime work and also 0.5 for previous employment. The following simulation shows number of equivalent days worked and also the equivalent time worked in Years, Months, and Days ...

y021020h1.xls
	A	B	C	D	E	F	G	H	I
1	FullTime		PartTime		PreviousEmployment
2	From	To	From	To	From	To		EquivDays	EquivYearsMonthsDays
3	5-Nov-1998	20-Oct-2002	23-Jan-1995	4-Nov-1998	15-Jul-1986	13-Jun-1998		4311	11years,9months,20days
Sheet6

</SPAN>

Please post back if it works for you -- otherwise explain a little futher and let us take it from there.

Regards!

Yogi

 

[CKMax]

This computation basically solves my problem, Yogi Anand. I went this Friday to the Functions and Formulas class and I thought I could improve on the current method (scratch paper and finger count, belive it or not). However, I have a question:
Knowing that not all months have the same number of days, how does Excel take into account this difference? Or if it only takes I3 as reference, how many days does it considers as a month?

I have some concern (liability) about shortchanging the applicant (the period of 02/02/2002 - 03/02/2002 does not have the same number of days as 03/02/2002 - 04/02/2002).

I just wanted some more information before I could distribute this (I will give full credit, of course). Thanks.

 

[Yogi Anand]

Hi CKMAx:

I believe you are aware that the date calendar calculations do not have the level of exactness than say as in summing up a series of simple numbers. The lack of exactness in date calculations is not only because of how Excel handles dates, but also because of the date calendars that we human beings have created.

So, to come directly to the point, the calculation I gave you is the state-of-the-art calculation, and working in different ways, working from different vintage points, you could expect a difference of 1 or so days -- and I don't believe that should be critical -- so here it is for whatever it is worth.

Regards!

Yogi

 

[CKMax]

Thank you, plettieri, maxflia10 (I will keep the formula for reference) and Yogi Anand for your answers.

Yogi: You are quite correct. We cannot expect an exact answer to everything given the differences in how we both (Excel and Humans) keep trak of time. You are also correct that a difference of a day is not critical given the task for which it is intended in this case. I just wanted an opinion as to how accurate it could be so as to give a point of reference to my higher-ups (I did not want to just say "because it works" if I were to be asked).

Coming to this forum made all the difference. I will pass the word around (and I am sure I will be back). Thank you all a LOT for your help.

CKMax