Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Odd or Even Year Formula (2003 SP3)

    I have a weird one. On the attached file, I have two tabs.

    1. On the Fleet Inventory tab, I would like to have a formula that will take the year of the model (Column G), refer to the last DIGIT in VIN No (Column K) to see if it's an odd or even number, and add 6 years to the year to have the results show in Column R. Basically, the law in our state says, that depending on the year of the car, a smog test is needed every 2 years after the car is 6 years old. If the VIN number ends in an odd number, then you need to have it smogged every odd year after the 6th year. If the VIN number ends in an even number, then you need to have it smogged every even year after the 6th year. That's what I am attempting to show in the one column to make it easier to tell when a vehicle is coming up for a smog check in the very large spreadsheet. Ideally, some logic in the formula so the date in the column in always the current or future year would be greatly appreciated. Hope that makes sense (it does in my head) and it is definitely over my Excel skills to figure out so thank you all in advance for even attempting it!

    2. A bit on the simpler side, In the Last Maintenance Date (Column P) and Type (Column Q), I would like a formula that will look on the next tab, and automatically pick the last date and the type of maintenance from the collection shown. The sample shows them in order which is not the case in the original file. If the second tab needs to be configured differently in order for this to work, I'm fine with that. Just inherited this project so I'm happy to move things around. On the current tab, the date is in the first row, the type of maintenance in the row directly beneath it.

    Again thank you all in advance.
    pooja
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Odd or Even Year Formula (2003 SP3)

    For question #1 in cell R2
    =IF(YEAR(TODAY())-G2>=6,YEAR(TODAY())+ABS(MOD(YEAR(TODAY()),2)-MOD(RIGHT(K2,2),2)),G2+ABS(MOD(G2,2)-MOD(RIGHT(K2,1),2))+6)

    Formula will calculate whether a Smog Check is due in the current year or next year, assuming vehicle is 6 years old or older. It also calculates the first year a Smog Check is due.

    Will work on question 2.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Odd or Even Year Formula (2003 SP3)

    Question #2 - assumes dates in chronological order from left to right with no extra data to right of maintenance data.:
    Formula in P2
    =OFFSET(Maintenance!B1,MATCH(A2,Maintenance!B:B,0)-1,COUNTA(INDIRECT("Maintenance!"&MATCH(A2,Maintena nce!B:B,0)&":"&MATCH(A2,Maintenance!B:B,0)))-1,1,1)

    Formula in Q2:
    =OFFSET(Maintenance!B1,MATCH(A2,Maintenance!B:B,0) ,COUNTA(INDIRECT("Maintenance!"&MATCH(A2,Maintenan ce!B:B,0)&":"&MATCH(A2,Maintenance!B:B,0)))-1,1,1)

    I've attached the workbook with the alterations.
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    210
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Odd or Even Year Formula (2003 SP3)

    Thank YOU much! I'm going to try them all out in my giant workbook and will let you know how it goes...

    pooja

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •