[Jason T.]

I am trying to set up a spread sheet that will calculate the first of the
month for the start of benefits.

The spread sheet has data is the cell C2 (Hire Date)

For example 12-1-2006 is the start date. I need a formula that will
calculate if C2 = The first of the month, the date returned is the first of
the same month, if C2 is any other date, it will return the first of the
following month.

I also need to take into consideration weekends and holidays.

Thanks for any assitance in advance.

[Kevin B]

Assuming that the first date is in cell A1, the following IF function will
return the desired result:

=IF(DAY(A1)=1,A1,DATE(YEAR(A1),MONTH(A1)+1,1))
--
Kevin Backmann


"Jason T." wrote:

I am trying to set up a spread sheet that will calculate the first of the
month for the start of benefits.

The spread sheet has data is the cell C2 (Hire Date)

For example 12-1-2006 is the start date. I need a formula that will
calculate if C2 = The first of the month, the date returned is the first of
the same month, if C2 is any other date, it will return the first of the
following month.

I also need to take into consideration weekends and holidays.

Thanks for any assitance in advance.

[ShaneDevenshire]

Hi Jason,

How exactly do you need to take holidays and weekends into consideration? -
do you need to include them or exclude them?

If you need to include them then:

=EOMONTH(A1,IF(DAY(A1)=1,-1,0))+1

This requires the Analysis ToolPak add-in in Excel 2003 and earlier. Choose
Tools, Add-Ins, and check Analysis ToolPak.

--
Cheers,
Shane Devenshire


"Jason T." wrote:

I am trying to set up a spread sheet that will calculate the first of the
month for the start of benefits.

The spread sheet has data is the cell C2 (Hire Date)

For example 12-1-2006 is the start date. I need a formula that will
calculate if C2 = The first of the month, the date returned is the first of
the same month, if C2 is any other date, it will return the first of the
following month.

I also need to take into consideration weekends and holidays.

Thanks for any assitance in advance.

[Jason T.]

Hello Kevin:

Thanks for responding so quickly!!

The dates are C2:C200, and putting information in D3:D200

A1 = Last name
B2 = Fist Name
C3 = Date of Hire

I changed all the A1's to C3's but I didn't get a date I got 39052.

I am using Excel 2007, of that makes a difference.

Once again thanks for responding!!

"Kevin B" wrote:

Assuming that the first date is in cell A1, the following IF function will
return the desired result:

=IF(DAY(A1)=1,A1,DATE(YEAR(A1),MONTH(A1)+1,1))
--
Kevin Backmann


"Jason T." wrote:

I am trying to set up a spread sheet that will calculate the first of the
month for the start of benefits.

The spread sheet has data is the cell C2 (Hire Date)

For example 12-1-2006 is the start date. I need a formula that will
calculate if C2 = The first of the month, the date returned is the first of
the same month, if C2 is any other date, it will return the first of the
following month.

I also need to take into consideration weekends and holidays.

Thanks for any assitance in advance.

[Jason T.]

Ahhh Shane this almost it!!

Basically I need this to be on 5 day work week schedule. So say is Sunday is
the first of the month, it will automatically pick Monday.

Don't worry about holidays if its hard to calculate them in the formula.

I am using Excel 2007

"ShaneDevenshire" wrote:

Hi Jason,

How exactly do you need to take holidays and weekends into consideration? -
do you need to include them or exclude them?

If you need to include them then:

=EOMONTH(A1,IF(DAY(A1)=1,-1,0))+1

This requires the Analysis ToolPak add-in in Excel 2003 and earlier. Choose
Tools, Add-Ins, and check Analysis ToolPak.

--
Cheers,
Shane Devenshire


"Jason T." wrote:

I am trying to set up a spread sheet that will calculate the first of the
month for the start of benefits.

The spread sheet has data is the cell C2 (Hire Date)

For example 12-1-2006 is the start date. I need a formula that will
calculate if C2 = The first of the month, the date returned is the first of
the same month, if C2 is any other date, it will return the first of the
following month.

I also need to take into consideration weekends and holidays.

Thanks for any assitance in advance.

[Jason T.]

Thanks for help Kevin and Shane.

I was able to use the formula Shane provided.

However I do need help with another formula.

Our 401K benefits begins the first of each quarter Jan 1., Apr 1., Jul 1.,
Oct 1.

Of course if an individual starts at the beginning of a quarter day 1, they
can begin 401K contributions. However any day after that, they have to start
in the next quarter.

The data is in cell C2 (Hire Date).

[Kevin B]

Jason, this is just an FYI.

The number that was returned by the formula I indicated earlier (39,000
something) is a date serial number. If you formatted the number it would
have displayed as a date and not as an integer.

The value you saw was the number of days that have elapsed since January 1,
1900.

Thanks...
--
Kevin Backmann


"Jason T." wrote:

Ahhh Shane this almost it!!

Basically I need this to be on 5 day work week schedule. So say is Sunday is
the first of the month, it will automatically pick Monday.

Don't worry about holidays if its hard to calculate them in the formula.

I am using Excel 2007

"ShaneDevenshire" wrote:

Hi Jason,

How exactly do you need to take holidays and weekends into consideration? -
do you need to include them or exclude them?

If you need to include them then:

=EOMONTH(A1,IF(DAY(A1)=1,-1,0))+1

This requires the Analysis ToolPak add-in in Excel 2003 and earlier. Choose
Tools, Add-Ins, and check Analysis ToolPak.

--
Cheers,
Shane Devenshire


"Jason T." wrote:

I am trying to set up a spread sheet that will calculate the first of the
month for the start of benefits.

The spread sheet has data is the cell C2 (Hire Date)

For example 12-1-2006 is the start date. I need a formula that will
calculate if C2 = The first of the month, the date returned is the first of
the same month, if C2 is any other date, it will return the first of the
following month.

I also need to take into consideration weekends and holidays.

Thanks for any assitance in advance.