Convert Excel formula to Excel

[dbazzelle]

Hello, would it be possible to convert this formula :

=IF(YEAR(B5)=YEAR(E$1),($D5/(DATEDIF($B5,$C5,"D")+1))*MIN((DATEDIF($B5,$C5,"D")+1),(DATEDIF(B5,EOMONTH(E$1,0),"D"))+1),IF(YEAR(C5)=YEAR(E$1),($D5/(DATEDIF($B5,$C5,"D")+1))*(((DATEDIF(B5,C5,"D")+1)-(DATEDIF(B5,DATE(YEAR(C5)-1,12,31),"D")+1)))))

to work in a calculated field in a query? I know my cell refrences would have to change to table and colum refrences, and I can handle that part. If you look at this post:

http://www.mrexcel.com/board2/viewtopic.php?t=79169&postdays=0&postorder=asc&start=10

you can see what I am working on. Thank you.

David

 

[MartinK]

Hi David,
I think so.

You have all the functions in Access (VBA): Datediff, Year, EOMONTH will be replaced by DateSerial with 0 as day.

E.g. dateserial(2004,2,0) returns the last day in January 2004.

Martin