DateTime Math

[David P. Donahue]

Can anyone think of an easy way to take two DateTime values and
calculate, with double precision, how many semi-monthly (the first and
the fifteenth) pay periods occur between them?

I started manually writing a function for it, and it just got uglier and
uglier. And I figure it's something that someone has probably figured
out before. Any ideas?


Regards,
David P. Donahue
(e-mail address removed)
http://www.cyber0ne.com

 

[Kevin Spencer]

Shouldn't be too hard. Of course, you begin by using DateTime.Subtract to
get the TimeSpan between the 2 dates.

The tricky part is calculating the fractions of pay periods that occur. This
is difficult because months are not the same number of days long. Some are
30, some are 31, and February and leap year are special cases. So, you have
to figure out your rules for determining what constitutes a "full pay
period." Is it exactly half of the average length of a month in
double-precision floating point days? Or do you calculate it on a
month-by-month basis, based upon the first 15 days of the month, and the
remaining number of days in that month? How you will calculate this depends
upon the business rule here.

Except for the first and last month, all months between them will contain
exactly 2.0 pay periods, since every month in the Calendar is more than 15
days long, even during leap year! ;-) So, your only real task is figuring
out the "left-overs," the days after the day of the month of the first
DateTime, and the days before the day of the month of the second DateTime.
For example, if the beginning and end DateTimes were Feb 1, 2006 and May 28,
2006, there will be 2 full months between the 2 Dates: March and April.
These 2 months constitute 2 * 2.0D (4.0D) pay periods.

So, you need to determine the Day of the month for the beginning and ending
DateTimes. Again, depending upon your business rule for determining the
length of the second pay period, the remainder will vary. If, for example,
you determine that a pay period constitues an average of 15.345 days (I made
up that number), you start with the first date (Feb 1, 2006) and subtract 1
from 28 (or 29, depending upon the year), take the result, and divide it by
15.345. Assuming a 28-day month, that leaves 27 days / 15.345 =
1.7595307917888563049853372434018 pay periods, which is added to 4 =
5.759530791788856304985337243401 pay periods, not counting the end. Now you
do the same for the second DateTime (May 28, 2006). For this we simply
divide 28 by 15.345 = 1.8246985988921472792440534376018 pay periods, added
to the existing 5.759530791788856304985337243401 pay periods =
7.584229390681003584229390681002 total pay periods.

Hopefully, if your business rule is different, such as using 15 for the
first pay period, and the remaining days in the month for the second, you
should be able to apply the same basic business rules I've provided to
develop your own algorithm.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Chicken Salad Alchemist

Big thicks are made up of lots of little thins.

 

[David P. Donahue]

How you will calculate this depends

upon the business rule here.

I guess that's the mouthful right there. The rest of your logic is
simple enough, and will be helpful. I guess at this point I just need
to wait until Monday and ask the client for some more specific business
rules in determining a pay period. Is it inclusive or exclusive of the
begin/end dates in the equation? Is it determined by 1-14, 15+ or by
1/2 month? etc.

At the very least, you've given me the right questions to ask them
tomorrow. Thanks!


Regards,
David P. Donahue
(e-mail address removed)
http://www.cyber0ne.com

 

[chanmm]

Correct me if I am wrong. One month you pay twice. Say 17 April to 13 June.
Mean April you will not pay again, May pay 2 times and June pay 1 time,
right? So you basically only need to know the different of the month and
whether the first date and the last date is more than 15 or less then should
be pretty simple.

chanmm

 

[William Stacey [MVP]]

right. Is it every 2 weeks on thursday (for example) or is it exactly on
the 1st and 15th of each month. If the latter, then that is easy, just
count the number of whole months and multiply by 2. Also special case any
parcial start/end dates. So if last date given is the 14ths, then there
will be only 1 pay period in that month or if first date given is the 16th,
then there is no pay periods for that month - etc.

--
William Stacey [MVP]

|> How you will calculate this depends
| > upon the business rule here.
|
| I guess that's the mouthful right there. The rest of your logic is
| simple enough, and will be helpful. I guess at this point I just need
| to wait until Monday and ask the client for some more specific business
| rules in determining a pay period. Is it inclusive or exclusive of the
| begin/end dates in the equation? Is it determined by 1-14, 15+ or by
| 1/2 month? etc.
|
| At the very least, you've given me the right questions to ask them
| tomorrow. Thanks!
|
|
| Regards,
| David P. Donahue
| (e-mail address removed)
| http://www.cyber0ne.com

 

[David P. Donahue]

Correct me if I am wrong. One month you pay twice. Say 17 April to 13 June.

Mean April you will not pay again, May pay 2 times and June pay 1 time,
right? So you basically only need to know the different of the month and
whether the first date and the last date is more than 15 or less then should
be pretty simple.

If all I needed was integer precision, sure. That's pretty much what
their old software does But the reason I'm calculating this figure
is to use it to project dollar amounts based on employee salaries, and
always rounding down like that just won't do.


Regards,
David P. Donahue
(e-mail address removed)
http://www.cyber0ne.com

 

[David P. Donahue]

if first date given is the 16th,

then there is no pay periods for that month

Nope, there would be a fractional pay period for that month. If someone
works from the 1st to the 25th in a given month, you don't stop paying
them on the 15th. Therein lies the challenge. I was hoping there would
be some way to use the DateTime object, or anything else in .NET, to
subtract date1 from date2 and get a precise result in months as a double.


Regards,
David P. Donahue
(e-mail address removed)
http://www.cyber0ne.com

 

[Larry Lard]

Nope, there would be a fractional pay period for that month. If someone
works from the 1st to the 25th in a given month, you don't stop paying
them on the 15th. Therein lies the challenge. I was hoping there would
be some way to use the DateTime object, or anything else in .NET, to
subtract date1 from date2 and get a precise result in months as a double.

That would be nice, but unfortunately is impossible. Thinking generally
here (not just considering what the framework does), when you subtract
a date from a date you lose all the context of those dates, and just
get a number of *days*. Without context, a number of days can't be
converted to any higher unit. Consider: how many months is 28 days?
Well, it depends *which* 28 days: the 28 days from Feb 1 2006 through
Feb 28 2006 are exactly 1 month, but the 28 days from Mar 1 2006
through Mar 28 2006 are ~ 0.9032 months! You get the same problem with
years - how many years is 366 days?!

As Kevin Spencer has already pointed out, "How you will calculate this
depends
upon the business rule here". It may well be that the *simplest*
(though not most elegant) way of doing this turns out to be something
like an iteration that starts at the earlier date, adds a day at a
time, incrementing a pay-period counter when appropriate, and stopping
at the later date. Don't knock yourself out trying to come up with the
one magic formula that does it all - remember you might have to
maintain this code a year down the line...

 

[William Stacey [MVP]]

That is not the original question however. IIRC, you wanted pay periods.
Now it seems you may want the number of days worked so you can pay them for
days worked. I am confused.

--
William Stacey [MVP]

|> if first date given is the 16th,
| > then there is no pay periods for that month
|
| Nope, there would be a fractional pay period for that month. If someone
| works from the 1st to the 25th in a given month, you don't stop paying
| them on the 15th.

That does not seem to be the original question however. IIRC, you wanted
"pay periods". Now it seems you may want the number of days worked so you
can pay them for days worked. I am confused. Pay periods is not
fractional - it is an integer. I must be missing something. Could you
restate the requirements. TIA.

 

[David P. Donahue]

That is not the original question however. IIRC, you wanted pay periods.

Now it seems you may want the number of days worked so you can pay them for
days worked. I am confused.

Pay periods in the form of a double, with precision to two decimal
places. I don't know if this is the norm or not, but the way our system
works is that you have a net salary for a pay period, but the 2nd pay
period for a given month has a variable length, determined by the length
of that month. So working between the 20th and the 25th in February
will apparently yield a larger net pay than the same days in January.

Of course, nobody in our system only works for such a small amount of
time. However, there are plenty of cases where people are paid from two
or three different sources of funding in the budget, and those sources
can switch from one to another on any given day. The employee is paid
the same, but the budgets and finance department needs to track the
sources of funding per employee with more precision.

The more I looked into it today, I found that the most useful functions
will be the one that determines the number of days in a given month and
the one that determines whether or not a given year is a leap year.
Other than that, it'll likely be a bunch of conditionals and whatnot,
piecing together the end result.

Not as simple as subtracting the DateTime values and applying some
built-in method, but it won't be too difficult I guess. If I have to, I
can hard-code an array of the 24 given pay periods for a year, making
the proper exceptions for leap years of course.

In any event, I appreciate the feedback from the group. As it turns
out, Kevin Spencer's original points were on target. A couple emails
today showed me that the budgets and finance people don't have all their
business logic together. And they need to do that before I can do any
more coding.

(Of course, if the logic at the beginning of this post holds true, then
I'm going to be sure to plan any future unpaid time off accordingly.
The latter half of any 31-day month, whichever 31-day month has the
fewest weekend days between the days of 16 and 31 ideally, will yield
the least lost pay.)


Regards,
David P. Donahue
(e-mail address removed)

 

[William Stacey [MVP]]

Make sure to take any rounded off amounts ;-) (just kidding)

--
William Stacey [MVP]

|> That is not the original question however. IIRC, you wanted pay periods.
| > Now it seems you may want the number of days worked so you can pay them
for
| > days worked. I am confused.
|
| Pay periods in the form of a double, with precision to two decimal
| places. I don't know if this is the norm or not, but the way our system
| works is that you have a net salary for a pay period, but the 2nd pay
| period for a given month has a variable length, determined by the length
| of that month. So working between the 20th and the 25th in February
| will apparently yield a larger net pay than the same days in January.
|
| Of course, nobody in our system only works for such a small amount of
| time. However, there are plenty of cases where people are paid from two
| or three different sources of funding in the budget, and those sources
| can switch from one to another on any given day. The employee is paid
| the same, but the budgets and finance department needs to track the
| sources of funding per employee with more precision.
|
| The more I looked into it today, I found that the most useful functions
| will be the one that determines the number of days in a given month and
| the one that determines whether or not a given year is a leap year.
| Other than that, it'll likely be a bunch of conditionals and whatnot,
| piecing together the end result.
|
| Not as simple as subtracting the DateTime values and applying some
| built-in method, but it won't be too difficult I guess. If I have to, I
| can hard-code an array of the 24 given pay periods for a year, making
| the proper exceptions for leap years of course.
|
| In any event, I appreciate the feedback from the group. As it turns
| out, Kevin Spencer's original points were on target. A couple emails
| today showed me that the budgets and finance people don't have all their
| business logic together. And they need to do that before I can do any
| more coding.
|
| (Of course, if the logic at the beginning of this post holds true, then
| I'm going to be sure to plan any future unpaid time off accordingly.
| The latter half of any 31-day month, whichever 31-day month has the
| fewest weekend days between the days of 16 and 31 ideally, will yield
| the least lost pay.)
|
|
| Regards,
| David P. Donahue
| (e-mail address removed)