RE: Coverting a number to year/month/day format

[Tatakau]

You'll need to custom code this one, though it is difficult since the length
of months vary, and 25% of the time so do years. Of course, you can be
extremely tenacious about your code and come up with a rather precise
function, but you should be asking yourself if there is a better way of
accomplishing whatever it is you're trying to do.

function foobar(num as Integer) as String
dim years as integer
dim months as integer
dim days as integer
years = 0
months = 0
days = num
do until days < 365
years = years + 1
days = days - 365
loop
do until days < 30
months = months + 1
days = days - 30
loop
foobar = years & " years, " & months & " months, " & days & " days"
end function

Hope that helps!

Nick

"CC@DMA" wrote:

> I am trying to convert any number into a year/month/day format. For example,
> getting 366 to read "1 year/0 months/1 day" is this possible? If so, how do I
> do it?

[CC@DMA]

Thanks!

[James A. Fortune]

CC@DMA wrote:
> I am trying to convert any number into a year/month/day format. For example,
> getting 366 to read "1 year/0 months/1 day" is this possible? If so, how do I
> do it?

The only way such a number makes sense is within the context of a
reference date such as Date() or RefDate from which to base the calculation.

The value for the year is just like the birthday problem:

Int(Format(DateAdd('d', N, Date()), 'yyyy.mmdd') - Format(Date(),
'yyyy.mmdd'))

The elapsed month can be found like:

(12 + Int(Format(DateAdd('d', N, Date()), 'mm.dd') - Format(Date(),
'mm.dd'))) Mod 12

The elapsed days can be found like:

Case 1: Day(d1) > Day(d2) => RestOfDaysInMonthOfd1 + Day(d2)
Case 2: Day(d1) <= Day(d2) => Day(d2) - Day(d1)

RestOfDaysInMonthOfd1 = DaysInMonthOfd1 - Day(d1)

Both cases can be combined into:

Day(d2) - Day(d1) + Abs(Day(d1) > Day(d2)) * Day(DateSerial(Year(d1),
Month(d1) + 1, 0))

where d1 = Date() and d2 = DateAdd('d', N, Date()) so:

Day(DateAdd('d', N, Date())) - Day(Date()) + Abs(Day(Date()) >
Day(DateAdd('d', N, Date()))) * Day(DateSerial(Year(Date()),
Month(Date()) + 1, 0))

The resulting SQL looks like:

SELECT 366 AS N, Int(Format(DateAdd('d', N, Date()), 'yyyy.mmdd') -
Format(Date(), 'yyyy.mmdd')) & ' year(s)/' & (12 +
Int(Format(DateAdd('d', N, RefDate), 'mm.dd') - Format(RefDate,
'mm.dd'))) Mod 12 & ' month(s)/' & Day(DateAdd('d', N, Date())) -
Day(Date()) + Abs(Day(Date()) > Day(DateAdd('d', N, Date()))) *
Day(DateSerial(Year(Date()), Month(Date()) + 1, 0)) & ' day(s)' AS
ElapsedTime FROM tblAny;

When I run this on 12/16/05 I get:

N ElapsedTime
366 1 year(s)/0 month(s)/1 day(s)

You can use the IIf function to clean up the s's when a value is 1 if
desired. I didn't test this much but it seems to be close to what you
are looking for.

I also tried:

SELECT 322 AS N, #12/8/05# As RefDate, Int(Format(DateAdd('d', N,
RefDate), 'yyyy.mmdd') - Format(RefDate, 'yyyy.mmdd')) & ' year(s)/' &
(12 + Int(Format(DateAdd('d', N, RefDate), 'mm.dd') - Format(RefDate,
'mm.dd'))) Mod 12 & ' month(s)/' & Day(DateAdd('d', N, RefDate)) -
Day(RefDate) + Abs(Day(RefDate) > Day(DateAdd('d', N, RefDate))) *
Day(DateSerial(Year(RefDate), Month(RefDate) + 1, 0)) & ' day(s)' AS
ElapsedTime FROM tblAny;

and got:

N RefDate ElapsedTime
322 12/8/05 0 year(s)/10 month(s)/18 day(s)

Note that N and RefDate can be fields in tblAny and the following SQL
will return the appropriate ElapsedTime for the values in that record:

SELECT N, RefDate, Int(Format(DateAdd('d', N, RefDate), 'yyyy.mmdd') -
Format(RefDate, 'yyyy.mmdd')) & ' year(s)/' & (12 +
Int(Format(DateAdd('d', N, RefDate), 'mm.dd') - Format(RefDate,
'mm.dd'))) Mod 12 & ' month(s)/' & Day(DateAdd('d', N, RefDate)) -
Day(RefDate) + Abs(Day(RefDate) > Day(DateAdd('d', N, RefDate))) *
Day(DateSerial(Year(RefDate), Month(RefDate) + 1, 0)) & ' day(s)' AS
ElapsedTime FROM tblAny;

With N = 363 in a record and RefDate = 1/12/05 it gave:

ElapsedTime
0 year(s)/11 month(s)/29 day(s)

James A. Fortune
MPAP...@FortuneJames.com