Using
gives me the total difference in months, I would like to show the result as years and months rathes than total months.Code:=DateDiff("m",[Shot],Now())/12
Thanks Jim O
Using
gives me the total difference in months, I would like to show the result as years and months rathes than total months.Code:=DateDiff("m",[Shot],Now())/12
Thanks Jim O
Issue is How many days in a month? Since they vary, it isn't a simple arithmetic thing.
You could work in days and divide by some factor, but again different number of days per month.
But, if you divide by 365 or 365.25 you could get an approximation of years and part years.
Guess it really depends on your requirement.
Now() gives Date and Time--is that required?
If you use Date(), you get date only.
maybe datediff("yyyy",[Shot],date) & " - " & int(datediff("m",[Shot],date)/12) ?
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Displays total months but I would like to see full years and months rounded to the nearest month. 20.6 would become 20 yr 7 mo.Code:=DateDiff("m",[BP],Now())/12
That is addressed to whom? If the requirement has changed to include part months greater than .5 then swap the Int for Round and see if that works for you.
datediff("yyyy",[Shot],date) & " - " & Round(datediff("m",[Shot],date)/12)
Rounding can produce unexpected results but should be satisfactory for this.
I concocted this to work with Dates less than today (eg historic)
which produced this with some sample datesCode:Int(DateDiff("m", shot, Now()) / 12) & " years and " & _ Int(((DateDiff("m", shot, Now) / 12) - (Int(DateDiff("m", shot, Now()) / 12))) * 365) \ 30 & " month(s)"
Period
from 21-Aug-97 till 16-Sep-19 Is 22 years and 1 month(s)
from 16-Sep-17 till 16-Sep-19 Is 2 years and 0 month(s)
from 06-Apr-09 till 16-Sep-19 Is 10 years and 5 month(s)
from 21-Jan-19 till 16-Sep-19 Is 0 years and 8 month(s)
Thank you very much, that works great.
Jim O