Excel Formula to Access Query - Time Between 2 Dates in Weeks

D

dmasch

I have an excel formula that calculates the # of weeks between two date fields:
=IF(AND(A14="",B14=""),"",IF(AND(B14="",A14>0),(NOW()-A14)/7,IF(AND(A14>0,B14>0),IF(A14=B14,0.14,(B14-A14)/7))))
Where B=Date2 and A=Date1. Not all date fields have a value and sometimes
the two date fields are the same. Here are the possibilities and the desired
result:

Date1 Date2 Result
Null Null Null
x y (y-x)/7
x x 0.14
x Null (Today()-x)/7

So I want to convert my Excel formula to an Access Query using Access 2002.
I have done some simple IIf statements but get very mixed up if I try to
nest. Any suggestions on where to look for the structure of formulas in
Access? Excel makes it so easy with the If(logical_test,[true],[false])
guide there for you. Thanks!
 
K

KARL DEWEY

Here is two ways to do it ---
SELECT Your_Table.Date1, Your_Table.Date2, DateDiff("w",[Date1],IIf([Date2]
Is Null,Date(),[Date2])) AS [Whole Weeks], DateDiff("d",[Date1],IIf([Date2]
Is Null,Date(),[Date2]))/7 AS [Fractional Weeks]
FROM Your_Table;
 
M

Michel Walsh

iif( Date2=Date1, 0.14, ( Nz(Date2, Date() ) - Date1 ) / 7 )



Hoping it may help,
Vanderghast, Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top