Is it possible to use a formula to get the starting and ending date of a period according to the Period number and Period Year we typed in another cell?
for example we fill in period "9" and Year "2011" .
regards,
robbert
Date
Votes
3 comments
-
Jet Reports Historic Posts Is it possible to use a formula to get the starting and ending date of a period according to the Period number and Period Year we typed in another cell?
for example we fill in period "9" and Year "2011" .
regards,
robbert
ELMO: Sofar I use Vert.Lookup in Excel to a reference table with the period dates and numbers in Excel.
Any better way would be welcome! -
Jet Reports Historic Posts This should work where A1=YEAR (e.g. 2014) and A2=MONTH No. (e.g. 9).
Start of month: =EOMONTH((($A$1-1900)*365.3+($A$2-1)*30.5),-1)+1
End of month: =EOMONTH((($A$1-1900)*365.3+($A$2-1)*30.5),0) -
David Stockill If you have non standard periods setup in Accounting Periods in NAV use the following to return start and end dates of current periods.
Beginning of Period: =NL("last","Accounting Period","Starting Date","Starting Date",".."&TODAY(),"Closed","False")
End of Period: =NL("first","Accounting Period","Starting Date","Starting Date",TODAY()&"..","Closed","False")-1
Please sign in to leave a comment.