Calculate age from weeks to years? 3

[donnadee]

We had our web-based application recently upgraded, and now it exports children's ages to a text file by weeks. It is frustrating.

Ex. You enter the child as 4 years and it exports as 48 weeks. If you enter the child as 4 weeks, it exports as 4 weeks. If you enter the child as 4 months, it exports as 16 weeks.

How do I take this data from in the reverse (weeks back into years) in a Access 97 database format.

What kind of formula (that would be smart enough) to turn

6 weeks into 1 month 2 weeks
16 weeks into 4 months
48 weeks into 4 years
54 weeks into 4 years 6 months

We are using Access 97.

Any help you can give will be greatly appreciated.

 

[PaulBricker]

I am assuming that you realize that 48 weeks is not 4 years but rather less than one year. So if I'm reading this correctly, you could put this in a query and feed it the [NumberofWeeks] value. Try this and see if it flys for you.

Age:Int([NumberofWeeks]/52)& " years " & Int(([NumberofWeeks] Mod 52)/4.3) & " months " & Int((([NumberofWeeks Mod 52)/4.3 - Int(([NumberofWeeks] Mod 52)/4.3))* 4) & " week(s) "


Paul

 

[randysmid]

Hi,
The formula is missing a bracket. Here is the corrected version:

Age:Int([NumberofWeeks]/52)& " years " & Int(([NumberofWeeks] Mod 52)/4.3) & " months " & Int((([NumberofWeeks] Mod 52)/4.3 - Int(([NumberofWeeks] Mod 52)/4.3))* 4) & " week(s) "

BUT, the peculiar thing is that if I use 53 weeks, I end up with 1 year 0 months 0 weeks.
Any idea why?
HTH,
Randy Smith
California Teachers Association

 

[PaulBricker]

Probably because I lost two things when I changed by test values to NumberofWeeks. The final 4 should be 4.3 so here is the corrected/corrected. Thanks Randy.

Age:Int([NumberofWeeks]/52)& " years " & Int(([NumberofWeeks] Mod 52)/4.3) & " months " & Int((([NumberofWeeks] Mod 52)/4.3 - Int(([NumberofWeeks] Mod 52)/4.3))* 4.3) & " week(s) "

Paul

P.S. 4.3 weeks per month isn't an exact science either. It drifts a little over time but I assume that the ages probably don't get big enough to really skew the results.

 

[randysmid]

Paul,
Very nice code - it deserves a star!!!
Randy HTH,
Randy Smith
California Teachers Association

 

[PaulBricker]

Thanks Randy. Now lets see if it works for donnadee.

Paul

 

[donnadee]

Thanks for the help.

It says I have an open parenthesis somewhere in the paragraph. any ideas?

 

[randysmid]

Hi Donnadee,
Did you use the last code statement from Paul? I did, and it works beautifully!!!
HTH,
Randy Smith
California Teachers Association

 

[donnadee]

I think I almost have it. The weeks are showing up as negative figures. is there any way to correct that?

Thanks so much for helping me on a Friday night.

 

[PaulBricker]

Post what you have for code so we can see it.

Paul

 

[donnadee]

Minimum Age: Int([Minimum Age Range]/52) & " yrs " & Int(([Minimum Age Range] Mod 52)/4.3) & " mos " & Int(([Minimum Age Range] Mod 52)/4.3-Int(([Minimum Age Range] Mod 52)/4.3)*4.3) & " wks "

I had copied the code directly from the site, but it wouldn't use it. It kept saying I had to many (

 

[donnadee]

I finally got it ! ! !

Thank you both Soooooooooo Much.

I truly truly Appreciate it !

 

[PaulBricker]

That's great. It's easy to lose a bracket when you copy and paste or edit code.

Paul