[RESOLVED] DateDiff Month

[kfcSmitty]

Is there a way to make datediff for months show a decimal number? I need to find out partial dates.

The user will enter a value such as 4/1 (mm/dd) and 12/5(mm/dd) and I need to find out the monthly value between it.

I was thinking of counting the days and dividing by 30..but that wouldn't be very pretty.

also weeks wouldnt work very well as sometimes theyre only partial weeks..

[RobDog888]

So your trying to find a decimal for number of days or weeks?

[kfcSmitty]

Sorry, Guess I was unclear :/

I want to find out the # of months in decimal form.


So if a user entered "4/1" and "5/15" it would give me like 1.5 months or something along those lines

[MartinLiss]

What's wrong with dividing by 30 and formatting the output to the nearest tenth?

[kfcSmitty]

What's wrong with dividing by 30 and formatting the output to the nearest tenth?

If it comes down to that I will have to do it...But I am doing a calculation and i'm kinda being stingy on it, so the closer I can get the monthly difference value, the more accurate the calculation will be

[RobDog888]

It can get messy real fast with datediff on days then check the number of days in that month to calculate a decimal fraction of months. How accurate do you need it or the extent of what you want to do?

[MartinLiss]

MsgBox Format((DateDiff("d", "04/01/05", "05/15/05") / 30), "#.#")

Given that a month may have anywhere from 28 to 31 days, I don't think you can be more accurate than that.

[RobDog888]

Ok, you can do it in three parts. First calculate the datediff using the "m" identifier to get the whole number of months difference. Then second, you calculate the fractional portion with a datediff using the "d" identifier but for only the part that is after the whole complete number of months. Then third you can get that fractional part and convert it to decimal after you calculate the number of days in that ending month.

[kfcSmitty]

Accurate enough...Basically what I am doing, is I have an employees annual salary..say $74,132.32

Then, they have splits..IE From "4/1" until "8/15" they will work 45% in department 03032

So I need to take $74,132.32 and multiply it by 0.3 * (("8/11" - "4/1") / 12)

actually...that might have just solved my problem...

[MartinLiss]

I think you should calculate the salary per day and multiply that by the total number of days worked.

[RobDog888]

Yes, easier but then you need to take into account weekends or work days only.

[kfcSmitty]

I think you should calculate the salary per day and multiply that by the total number of days worked.

Thats how I am getting annual salary...but to get 15 different splits (its possible) and then figure out how many increases the user receives..and their anniversary (pay increase)....its a headache

Guess I will just divide by 30..thanks to the both of ya

[dglienna]

Somthing like this. Fraction of a month isn't hard, but what I did was subtract the fraction of the first month, and added the fraction of the second month. Not sure if that's right, though.

Option Explicit
 
Private Sub Form_Load()
  Dim s As Date, t As Integer, u As Long, v As Long
  Dim u1 As Long, v1 As Long
  ' 2 months
  t = DateDiff("m", Now, DateSerial(5, 10, 5)) ' months
  ' u = days part
  ' v = days in month
  u = CDbl(DateDiff("d", DateSerial(Year(Now), Month(Now), 1), Now))
  v = CDbl(Day(DateSerial(Year(Now), Month(Now) + 1, 0)))
  
  u1 = CDbl(10) ' second date days
  v1 = CDbl(Day(DateSerial(5, 10 + 1, 0)))
  ' 2 - 17/31 + 10/31 = 1.77 months
  MsgBox Format(t - (u / v) + (u1 / v1), "0.##")
  
End Sub

[MartinLiss]

When dealing with salary you can calculate the daily salary, and calulating days worked is easy, so why would you want to do anything but what I previously suggested?

[kfcSmitty]

When dealing with salary you can calculate the daily salary, and calulating days worked is easy, so why would you want to do anything but what I previously suggested?

Because I am not just calculating their work days. I have to do several different calculations for which department they would be in, or what % they have allocated to each program.