Time Elapse Formula

[kwooten]

I have 2 dates that i need to make a formula with and have used datediff for the most part. now i have an issue if the time period crosses years. For example, i need my formula to give me partial $ information based on year.



FROMDATE: 12/14/2012
TODATE: 3/18/2013

Expense: 64,870

I need the expense related to 1/1/2013 to 3/18/2013 in one column and 12/14/2012 to 12/31/2012 in another.. i have done it before but cannot find the query and believe it was a very rustic formula consisting of quite a few IIF statements.

any ideas on how to make this as simple as possible?

[June7]

Maybe like:

BETWEEN [FromDate] AND IIf(Year([FromDate])=Year([ToDate]), [ToDate], "12/31/" & Year([FromDate]))

BETWEEN IIf(Year([FROMDate])=Year([ToDate]), [FromDate], "1/1/" & Year([ToDate])) AND [ToDate])

[kwooten]

yes but need the $ associated with the "YTD" expense. For 2013 the $ should be 13,507.18 ((64,870/12)*(((#3/18/2013#-#1/1/2013#)/365)*12)) which is the expense related to the time between 1/1/2013 and 3/18/2013; 3,021.34 for 12/14/2012-12/31/2012..

[kwooten]

Maybe like:

BETWEEN [FromDate] AND IIf(Year([FromDate])=Year([ToDate]), [ToDate], "12/31/" & Year([FromDate]))

BETWEEN IIf(Year([FROMDate])=Year([ToDate]), [FromDate], "1/1/" & Year([ToDate])) AND [ToDate])

Invalid Syntax on the first between statement? Entered a comma without preceding value or identifier...

[June7]

In GROUP BY query, try:

IIf([fieldname] BETWEEN [FromDate] AND IIf(Year([FromDate])=Year([ToDate]), [ToDate], "12/31/" & Year([FromDate])), [Amount], 0)

Then set Total row for that constructed field to Sum.

Alternative maybe expression in textbox in group footer section of report:
=Sum(IIf([fieldname] BETWEEN [FromDate] AND IIf(Year([FromDate])=Year([ToDate]), [ToDate], "12/31/" & Year([FromDate])), [Amount], 0))