date - Access datediff using calculated first day of the year -
i'm creating new form in access 2010. need create field calculate year date timeframe, in months, using calculated value first day of year. scenario user input date. then, access calculate value months ytd, based on date user keys. catch user may input date current year, or date previous year. so, cannot hard code baseline date, 1/1/2014, perform calculation. need access generate first day of year, based on date entered, perform calculation.
example: user enters '4/10/2013'
access calculates months ytd '1/1/2013' '4/10/2013'.
expected result: 3.25 months
i need assist calculating income, utilize dates on multiple years.
i created function you:
public function calcmonths(datecalc date) double calcmonths = round(datediff("d", dateserial(year(datecalc), 1, 1), datecalc) * 12 / datediff("d", dateserial(year(datecalc), 1, 1), dateserial(year(datecalc) + 1, 1, 1)), 2) end function
you can use input form. calculates first day of entered year dateserial(year(datecalc), 1, 1)
, calculates difference in days. result multiplied 12 (months) , divided number of days within year (calculated make sure leap years 366 days). in end result rounded 2 decimal numbers.
Comments
Post a Comment