Link to home
Start Free TrialLog in
Avatar of lorijgreen
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!
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Hi,
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
PS:
I suspect that Current would be something like:
=Nz([Last],Nz([4th],Nz([3rd],Nz([2nd],Nz([1st],Nz([Initial],0))))))
Regards,
Rory
Avatar of lorijgreen
lorijgreen

ASKER

Hi Rory,
I have to go kick my user out of the database so I can try =Nz([Last],Nz([4th],Nz([3rd],Nz([2nd],Nz([1st],Nz([Initial],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
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
GOT IT! THANKS SO MUCH.