# Prorated dates.

#### CKMax

##### New Member
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

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### plettieri

##### Well-known Member
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

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
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
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
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
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

Replies
5
Views
215
Replies
3
Views
316
Replies
3
Views
164
Replies
0
Views
301
Replies
2
Views
318

1,181,108
Messages
5,928,104
Members
436,588
Latest member
mummabare

### 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.

### Which adblocker are you using?

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

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