lorijgreen
asked on
Convert Excel Formula to Access =FLOOR(SUM(NOW()-P2),1), =Current(I2:N2), =age(O2,G2, J2:M2), =SUM(H19+K19+M19)
Hi I inherited this spreadsheet and I need to have the same functionality in MS Access. It is used to determine vehicle pricing and dates in inventory. The pricing person quit yesterday and I know nothing about formulas but here they are:
Age
=FLOOR(SUM(NOW()-P2),1)
Current
=Current(I2:N2)
Status
=age(O2,G2, J2:M2)
Basic Sum
=SUM(H19+K19+M19)
Column Headings are A through X
Franchise Stock Desc Certified Color Mileage S.L. Cost Initial 1st 2nd 3rd 4th Last Age Entered Leaving Current Status Blue Book AcutalDesc Year Sold Payment
Toyota 11223648 01 CENTURY SILVER 62 60 $5,673 $7,233 48 6/2/06 1-Aug #NAME? #NAME? $7,925 BUICK CENTURY 2001 148-158
Volvo 12036943 01 S60 ASH GOLD 44 60 $14,161 $16,780 $16,550 $16,056 308 9/15/05 14-Nov #NAME? #NAME? VOLVO S60 2001
Ford 12314462 01 C2500 RED 86 60 $10,195 $13,826 23 6/27/06 26-Aug #NAME? #NAME? $15,310 CHEVY C2500 2001 297-307
I'm trying to look this up and figure it out myself but if ANYONE CAN HELP PLEASE!
Age
=FLOOR(SUM(NOW()-P2),1)
Current
=Current(I2:N2)
Status
=age(O2,G2, J2:M2)
Basic Sum
=SUM(H19+K19+M19)
Column Headings are A through X
Franchise Stock Desc Certified Color Mileage S.L. Cost Initial 1st 2nd 3rd 4th Last Age Entered Leaving Current Status Blue Book AcutalDesc Year Sold Payment
Toyota 11223648 01 CENTURY SILVER 62 60 $5,673 $7,233 48 6/2/06 1-Aug #NAME? #NAME? $7,925 BUICK CENTURY 2001 148-158
Volvo 12036943 01 S60 ASH GOLD 44 60 $14,161 $16,780 $16,550 $16,056 308 9/15/05 14-Nov #NAME? #NAME? VOLVO S60 2001
Ford 12314462 01 C2500 RED 86 60 $10,195 $13,826 23 6/27/06 26-Aug #NAME? #NAME? $15,310 CHEVY C2500 2001 297-307
I'm trying to look this up and figure it out myself but if ANYONE CAN HELP PLEASE!
PS:
I suspect that Current would be something like:
=Nz([Last],Nz([4th],Nz([3r d],Nz([2nd ],Nz([1st] ,Nz([Initi al],0))))) )
Regards,
Rory
I suspect that Current would be something like:
=Nz([Last],Nz([4th],Nz([3r
Regards,
Rory
ASKER
Hi Rory,
I have to go kick my user out of the database so I can try =Nz([Last],Nz([4th],Nz([3r d],Nz([2nd ],Nz([1st] ,Nz([Initi al],0))))) )
out.
From my very limited understanding all I have to do is create new module with that correct?
I will also check the spreadsheet to see where he has hidden any other coding.
I am so kicking myself for not taking any access classes. I feel so stupid.
thank you so much.
lori
I have to go kick my user out of the database so I can try =Nz([Last],Nz([4th],Nz([3r
out.
From my very limited understanding all I have to do is create new module with that correct?
I will also check the spreadsheet to see where he has hidden any other coding.
I am so kicking myself for not taking any access classes. I feel so stupid.
thank you so much.
lori
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
GOT IT! THANKS SO MUCH.
I believe the equivalents are:
1. =Int(Now-[Entered])
2. Current is not a built-in worksheet function - there must be some code in your workbook.
3. The same applies for Age - not an Excel function.
4. =[Cost]+[2nd]+[4th]
HTH
Rory