Attempts to track partial years of employment; intriguing formulas, further expansion

One sometimes has to keep track of when and how much a person works during a fiscal year. One prefers to keep track of fractional periods of fiscal years.

http://www.excel-easy.com/examples/day-of-the-year.html

http://excelsemipro.com/2011/01/what...he-year-is-it/

http://www.mrexcel.com/archive/Formulas/7749.html

http://www.excelforum.com/excel-form...r-of-year.html

Perhaps somewhat curiously, Excel lacks a function to return the number of a day in the year. For example, January 1 (1/1) serves as Day 1 of a calendar year, January 31 (1/31) serves as Day 31 of a calendar year. Leap years complicate this situation minimally, adding just an extra day. An Excel formula follows:

= A2-DATE(YEAR(A2),1,0)

=(A1-DATE(YEAR(A1),1,0))/(365+ISNUMBER(("2/29/"&YEAR(A1))+0))

=A1-DATE(YEAR(A1), 1, 0)

Sometimes fiscal years present somewhat complicated calculations. September 30 (9/30) usually occurs as roughly the two hundred seventieth (270th) day of the year. A fiscal year commences accordingly on October 1 (10/1).

Some key terms:

DY=Day of Year

DOH=Date of Hire

DOT=Date of Termination

So, if a person commences work after October 1 (10/1), one proceeds accordingly:

(365-DYDOH+270)/365

If a person commences work prior to October 1 (10/1), one proceeds as follows:

(270-DYDOH)/365

If the DOT occurs prior to October 1 (10/1), then one resorts to the following:

(270-DYDOT+270)/365

If the DOT occurs after October 1 (10/1), then one resorts to the following:

(DYDOT-270)/365

Date functions in Excel allow one to quite easily identify when 10/1 occurs in a particular calendar year. This helps attend to the complications of leap years. One uses DATE(year,month,day) with month and day set as October 1 (10/1); one could name a cell to contain October 1 of any year (10/1) as a set reference point. For the year, the formulas above indicate that one may have to use a prior year or a subsequent year, so one should use the Year function with one (1) added or subtracted as appropriate.
_________________________________________________________________________________________________

Some approaches or complications that occurred to me:

1. Can anyone think of a somewhat simple formula that quantifies the number of whole years occurring amongst a person's DOH and DOT?

2. I have the notion of setting up perhaps a separate sheet in the workbook or Excel file that would using information flowing from another worksheet. One would enter the data on an individual, for example, Margo L---, with DOT, DOH, etc. This information would flow to a separate sheet. This separate sheet would then take that information and prepare in columns the following:

One column would feature the first day of the fiscal year in which the person commenced employment, and the adjacent column would feature the end of this respective fiscal year. A third column would feature the fraction value of the part of the fiscal year that the person worked (so if the person worked 3/4 of the fiscal year, .75). This would continue until the DOT, with in that column (though in obviously a different row) the first day of the fiscal year containing the DOT and in the adjacent column, the last day of the fiscal year containing the DOT. A third column would contain the fractional part of the fiscal year that the person worked in the fiscal year containing the DOT (so if the person worked 3/4 of the fiscal year, .75).

One could also have in further columns the respective commencement dates of subsequent fiscal years and respective end dates of subsequent fiscal years. This could continue for many subsequent years, with perhaps these subsequent rows serving as areas to insert data should the person ever return to work with the same employer.

Difference of two dates in years
=datedif(doh,dot,"y")

For the remaining days

=DATEDIF(doh,dot,"d")-DATEDIF(doh,dot,"Y")*365

For the percentage of a year remaining

=(DATEDIF(doh,dot,"d")-DATEDIF(doh,dot,"Y")*365)/365

Hi,

See if this site helps with your problem:

http://www.excel-2010.com/excel-datedif/