Fiscal Date default value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings all.
My body is at work, but my brain must be on vacation because I can't seem to
get this right. I have a form used to hold the beginning and ending dates for
a date range query. I need to use fiscal dating (22nd thru the 21st) I am
having an issue getting the begin date correct. If the current date is the
21st or less, I need the begin default value to be the 22nd of the PREVIOUS
month. On the 22nd or greater, I need the begin date to be the 22nd of the
CURRENT month. My logic is failing as this should be relatively easy.
My current default date string looks like this and sets the begin date to
the 1st of the current month:
=IIF(FORMAT(DATE(),"DD"=1,DATEADD("M",-1,DATE()),(DATE()-DAY(DATE())+1))
Format for value mm/dd/yy
HELP!
 
Drayton said:
Greetings all.
My body is at work, but my brain must be on vacation because I can't
seem to get this right. I have a form used to hold the beginning and
ending dates for a date range query. I need to use fiscal dating
(22nd thru the 21st) I am having an issue getting the begin date
correct. If the current date is the 21st or less, I need the begin
default value to be the 22nd of the PREVIOUS month. On the 22nd or
greater, I need the begin date to be the 22nd of the CURRENT month.
My logic is failing as this should be relatively easy. My current
default date string looks like this and sets the begin date to the
1st of the current month:
=IIF(FORMAT(DATE(),"DD"=1,DATEADD("M",-1,DATE()),(DATE()-DAY(DATE())+1))
Format for value mm/dd/yy
HELP!
Click to expand...

There may be a better way, but I think this does what you want:

=IIf(Day(Date())< 22,
DateSerial(Year(Date()), Month(Date()) - 1, 22),
DateSerial(Year(Date()), Month(Date()), 22))
 
Greetings all.
My body is at work, but my brain must be on vacation because I can't seem to
get this right. I have a form used to hold the beginning and ending dates for
a date range query. I need to use fiscal dating (22nd thru the 21st) I am
having an issue getting the begin date correct. If the current date is the
21st or less, I need the begin default value to be the 22nd of the PREVIOUS
month. On the 22nd or greater, I need the begin date to be the 22nd of the
CURRENT month. My logic is failing as this should be relatively easy.
My current default date string looks like this and sets the begin date to
the 1st of the current month:
=IIF(FORMAT(DATE(),"DD"=1,DATEADD("M",-1,DATE()),(DATE()-DAY(DATE())+1))
Format for value mm/dd/yy
HELP!
Click to expand...

You're doing this the HARD way!

DateSerial(Year(Date()), Month(Date()) - IIF(Day(Date() < 22, 1, 0),
22)


John W. Vinson[MVP]
 
Back
Top