How to add age table to see the Years of Age as at Today

[nevilledhamsiri]

Dear All!

Attached table carries the data as per Vehicle Register maintained & I look forward to bring in an age table to calculate the number of Years of each vehicle.If the Age > 5 Years I need that record to be highlighted with a back ground colour. Please help me on this.

Regds

Neville

[sunny_talwar · Accepted solution]

Created Age in the script

ASSET:

LOAD [BR_CODE (AS PER OUR REORDS)],

[Inward Date],

[Purchased Date],

Age(Today(), [Purchased Date]) as Age,

[Asset Ref. No.],

    [Asset ID No.], Asset_Description, [Dep. Comm ence Date], [Dep. Rate], [Opening Balance],

    Additions, Inwards, [Opening Balance1] ,  [Opening Balance]+  Additions+  Inwards AS Cost, IF( [Opening Balance]+  Additions+  Inwards >300000,'Car', 'Bike')as Vehicle_Type1, [Current Year Value], [Written Down Value],

    [Inward Transfer], [Outward Transfer],IF([Inward Transfer]>0,'Inward',IF([Outward Transfer]>0,'Out ward')) As Inward_Outward,  [Sales Value], [Profit/ Loss Value], [T.R. Value],

    [Net Written Down Value], STATUS ,IF(STATUS='GA','Garage',IF( STATUS='ID','Idling',IF( STATUS='IW','Inward',IF( STATUS='SD','Sold',IF(  STATUS='US','Usable',IF(STATUS='YD','Yard')))))) as New_Status, [Physically Left Branch], User, [Date kept at Yard]

FROM [MOTOR_VEHICLE_REGISTER.xlsx] (ooxml, embedded labels, table is Master);

and then used a background color expression

=If(Age > 5, Blue())

[nevilledhamsiri]

Thanks Sunny for your information but I think I should have done something wrong somewhere that may be why I am not getting respective ages against each vehicle. Could you please correct see where I have gone wrong!

Regds

ASSET:

LOAD [BR_CODE (AS PER OUR REORDS)], [Inward Date], [Purchased Date],  Age(Today(), [Purchased Date]) as Age,[Asset Ref. No.],

     [Asset ID No.], Asset_Description, [Dep. Comm ence Date], [Dep. Rate], [Opening Balance],

     Additions, Inwards, [Opening Balance1] ,   [Opening Balance]+  Additions+  Inwards AS Cost, IF( [Opening Balance]+  Additions+  Inwards >300000,'Car', 'Bike')as Vehicle_Type1, [Current Year Value], [Written Down Value],

     [Inward Transfer], [Outward Transfer],IF([Inward Transfer]>0,'Inward',IF([Outward Transfer]>0,'Out ward')) As Inward_Outward,  [Sales Value], [Profit/ Loss Value], [T.R. Value],

     [Net Written Down Value], STATUS ,IF(STATUS='GA','Garage',IF( STATUS='ID','Idling',IF( STATUS='IW','Inward',IF( STATUS='SD','Sold',IF(  STATUS='US','Usable',IF(STATUS='YD','Yard')))))) as New_Status, [Physically Left Branch], User, [Date kept at Yard]

FROM (ooxml, embedded labels, table is Master);

[nevilledhamsiri]

It seems that the difference of  date format. The age I am getting on format on MM/DD/YY. Could you please show me how this could be corrected!

[sunny_talwar]

Yes, that i what I changed in the script

SET DateFormat='DD-MM-YYYY';

[nevilledhamsiri]

Fine, It works

Thank so much

Neville