Calculate Date field

  • Thread starter Thread starter Connie
  • Start date Start date
C

Connie

I am attempting to create a field in a form that would
calculate the first day of the next month based on a field
called [HireDate]. I need to create a field that would
look at the [HireDate] and calculate 1 year from that
date, and then add the correct number of days to show the
first day of the next month. (i.e. [HireDate] = 4/12/02
[VacStart]= 5/1/03) This would be the first of the month
AFTER one year anniversary of HireDate. Any suggestions?
Thanks.
 
Connie said:
I am attempting to create a field in a form that would
calculate the first day of the next month based on a field
called [HireDate]. I need to create a field that would
look at the [HireDate] and calculate 1 year from that
date, and then add the correct number of days to show the
first day of the next month. (i.e. [HireDate] = 4/12/02
[VacStart]= 5/1/03) This would be the first of the month
AFTER one year anniversary of HireDate. Any suggestions?
Thanks.
Click to expand...

=DateSerial(Year([HireDate])+1, Month([HireDate])+1, 0)
 
-----Original Message-----
Connie said:
I am attempting to create a field in a form that would
calculate the first day of the next month based on a field
called [HireDate]. I need to create a field that would
look at the [HireDate] and calculate 1 year from that
date, and then add the correct number of days to show the
first day of the next month. (i.e. [HireDate] = 4/12/02
[VacStart]= 5/1/03) This would be the first of the month
AFTER one year anniversary of HireDate. Any suggestions?
Thanks.
Click to expand...

=DateSerial(Year([HireDate])+1, Month([HireDate])+1, 0)

Rick,
I just tried this calculation and it works great EXCEPT
Click to expand...
it gives me the last day of the month of the [HireDate].
(i.e. [HireDate] = 4/12/02, your calculation gives me the
date 4/30/03) What I need is 5/1/03..... So close, but
I'm not sure what to adjust. Thanks for your help!!
Connie
 
-----Original Message-----
Rick Brandt said:
I am attempting to create a field in a form that would
calculate the first day of the next month based on a field
called [HireDate]. I need to create a field that would
look at the [HireDate] and calculate 1 year from that
date, and then add the correct number of days to show the
first day of the next month. (i.e. [HireDate] = 4/12/02
[VacStart]= 5/1/03) This would be the first of the month
AFTER one year anniversary of HireDate. Any suggestions?
Thanks.
Click to expand...

=DateSerial(Year([HireDate])+1, Month([HireDate])+1, 0)
Click to expand...

Should have been...

=DateSerial(Year([HireDate])+1, Month([HireDate])+1, 1)


.TERRIFIC!! It worked great!! Thanks Rick! You're Tops!
Click to expand...
 
What about this?
VacStart= Month((DateAdd("m",1,[HireDate])) & "/1/" &
Year(DateAdd("yyyy",1[HireDate]))

(Not sure whether to use & or +)
 
Back
Top