Calculate date fields

SteveL13
SteveL13 used Ask the Experts™
on
If I have a date field on a form that is 11/19/2020 how can I calculate the first date of that same month in another form field?  In other words I want to display 11/1/2020 on the form.

And then also, how can I calculate the 3rd date field if 11/19/2020 is the 1st date and I want the 3rd date to show 1/31/2021 (2 months after the 1st date but going to the end of the month).
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Paul MacDonaldDirector, Information Systems

Commented:
One way would be something like:

     DateSerial(DatePart("yyyy", sourcedate), DatePart("mm", sourcedate), 1)

Author

Commented:
I'm getting Application Defined or Object Defined error.  Using:

Me.txtEarlyExitSurveyDueDateBegins = DateSerial(DatePart("yyyy", txtMemberEndDateTermOfOfService), DatePart("mm", txtMemberEndDateTermOfOfService), 1)

Author

Commented:
Plus I'm looking for two calculated dates.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Second date will be:

=DateSerial(Year(Me!txtMemberEndDateTermOfOfService.Value), Month(Me!txtMemberEndDateTermOfOfService.Value), 1)

Open in new window

Third date will be:

=DateSerial(Year(Me!txtMemberEndDateTermOfOfService.Value), Month(Me!txtMemberEndDateTermOfOfService.Value) + 3, 0) 

Open in new window

If your "dates" are text, convert to true date values first:

=DateSerial(Year(CDate(Me!txtMemberEndDateTermOfOfService.Value)), Month(CDate(Me!txtMemberEndDateTermOfOfService.Value)), 1) 

Open in new window

If Me!txtMemberEndDateTermOfOfService.Value can be Null, check for that first.

Author

Commented:
Gustav...  PERFECT!  Thank you.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial