First of Month, Last of Month

[Guest]

I want to create an IF statement to discover whether an entered date in a
table is the first or the last of a month, irrespective of the year. I need
the identification so that I can calculate from the entered date.

Is there someone who can help. I'd really appreciate it. Thanks.

 

[Ken Snell [MVP]]

Assuming name of field is [MyDate],

=IIf([MyDate]=DateSerial(Year([MyDate], Month([MyDate] + 1, 0), "The date is
last day of month", IIf([MyDate]=DateSerial(Year([MyDate], Month([MyDate],
1, "The date is first day of month", "The date is neither first nor last day
of month"))

 

[Guest]

Just to add to Ken's note: There is a great article on the Microsoft website
"Functions for Calculating and Displaying Date/Time Values" which addresses a
lot of these formulas.

Assuming name of field is [MyDate],

=IIf([MyDate]=DateSerial(Year([MyDate], Month([MyDate] + 1, 0), "The date is
last day of month", IIf([MyDate]=DateSerial(Year([MyDate], Month([MyDate],
1, "The date is first day of month", "The date is neither first nor last day
of month"))

--

Ken Snell
<MS ACCESS MVP>

I want to create an IF statement to discover whether an entered date in a
table is the first or the last of a month, irrespective of the year. I need
the identification so that I can calculate from the entered date.

Is there someone who can help. I'd really appreciate it. Thanks.

 

[Guest]

Thank you. Your answer was right on the money!

Assuming name of field is [MyDate],

=IIf([MyDate]=DateSerial(Year([MyDate], Month([MyDate] + 1, 0), "The date is
last day of month", IIf([MyDate]=DateSerial(Year([MyDate], Month([MyDate],
1, "The date is first day of month", "The date is neither first nor last day
of month"))

--

Ken Snell
<MS ACCESS MVP>

I want to create an IF statement to discover whether an entered date in a
table is the first or the last of a month, irrespective of the year. I need
the identification so that I can calculate from the entered date.

Is there someone who can help. I'd really appreciate it. Thanks.

 

[Ken Snell [MVP]]

You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Thank you. Your answer was right on the money!

Assuming name of field is [MyDate],

=IIf([MyDate]=DateSerial(Year([MyDate], Month([MyDate] + 1, 0), "The date is
last day of month", IIf([MyDate]=DateSerial(Year([MyDate], Month([MyDate],
1, "The date is first day of month", "The date is neither first nor last day
of month"))

--

Ken Snell
<MS ACCESS MVP>

I want to create an IF statement to discover whether an entered date in a
table is the first or the last of a month, irrespective of the year.

I
need

the identification so that I can calculate from the entered date.

Is there someone who can help. I'd really appreciate it. Thanks.