Prorated dates.

CKMax

New Member
Joined
Oct 19, 2002
Messages
14
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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

plettieri

Well-known Member
Joined
Sep 4, 2002
Messages
1,556
Platform
  1. MacOS
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
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
ABCDEFGHI
1FullTimePartTimePreviousEmployment
2FromToFromToFromToEquivDaysEquivYearsMonthsDays
35-Nov-199820-Oct-200223-Jan-19954-Nov-199815-Jul-198613-Jun-1998431111years,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

New Member
Joined
Oct 19, 2002
Messages
14

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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

New Member
Joined
Oct 19, 2002
Messages
14
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
 

Forum statistics

Threads
1,144,060
Messages
5,722,276
Members
422,420
Latest member
losc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top