Is there a funtion I can use to automatically pick up the first business day
of the month based on a given date?

For instance, if today is 5/14/08, I would like a function to pull in the
first business day of the month (5/1/08), based on 5/14/08.

I have tried using the Workday function, but this function requires you to
put in the number of holidays you would like to exclude and doesn't always
work out.

hi
=DATE(YEAR(TODAY()),MONTH(TODAY()),1)
regards
FSt1

"Needhelp!" wrote:

Is there a funtion I can use to automatically pick up the first business day
of the month based on a given date?

For instance, if today is 5/14/08, I would like a function to pull in the
first business day of the month (5/1/08), based on 5/14/08.

I have tried using the Workday function, but this function requires you to
put in the number of holidays you would like to exclude and doesn't always
work out.

Thank you for the response. But this formula does not account for weekends. I
am looking for the first business day of each month.

Thanks again.


"FSt1" wrote:

hi
=DATE(YEAR(TODAY()),MONTH(TODAY()),1)
regards
FSt1

"Needhelp!" wrote:

Is there a funtion I can use to automatically pick up the first business day
of the month based on a given date?

For instance, if today is 5/14/08, I would like a function to pull in the
first business day of the month (5/1/08), based on 5/14/08.

I have tried using the Workday function, but this function requires you to
put in the number of holidays you would like to exclude and doesn't always
work out.

With 5/14/08 in H16 try:

=H16-DAY(H16)-WEEKDAY(H16-DAY(H16),2)+8

or:

=TODAY()-DAY(TODAY())-WEEKDAY(TODAY()-DAY(TODAY()),2)+8

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Needhelp!" wrote in message
...
Thank you for the response. But this formula does not account for
weekends. I
am looking for the first business day of each month.

Thanks again.


"FSt1" wrote:

hi
=DATE(YEAR(TODAY()),MONTH(TODAY()),1)
regards
FSt1

"Needhelp!" wrote:

Is there a funtion I can use to automatically pick up the first
business day
of the month based on a given date?

For instance, if today is 5/14/08, I would like a function to pull in
the
first business day of the month (5/1/08), based on 5/14/08.

I have tried using the Workday function, but this function requires you
to
put in the number of holidays you would like to exclude and doesn't
always
work out.

See if this does what you want...

=DATE(YEAR(A5),MONTH(A5),1)+(3-MAX(0,WEEKDAY(A5,2)-5))*(WEEKDAY(A5,2)5)

Rick


"Needhelp!" wrote in message
...
Thank you for the response. But this formula does not account for
weekends. I
am looking for the first business day of each month.

Thanks again.


"FSt1" wrote:

hi
=DATE(YEAR(TODAY()),MONTH(TODAY()),1)
regards
FSt1

"Needhelp!" wrote:

Is there a funtion I can use to automatically pick up the first
business day
of the month based on a given date?

For instance, if today is 5/14/08, I would like a function to pull in
the
first business day of the month (5/1/08), based on 5/14/08.

I have tried using the Workday function, but this function requires you
to
put in the number of holidays you would like to exclude and doesn't
always
work out.

On Thu, 15 May 2008 13:46:04 -0700, Needhelp!
wrote:

Is there a funtion I can use to automatically pick up the first business day
of the month based on a given date?

For instance, if today is 5/14/08, I would like a function to pull in the
first business day of the month (5/1/08), based on 5/14/08.

I have tried using the Workday function, but this function requires you to
put in the number of holidays you would like to exclude and doesn't always
work out.

The WORKDAY function should work fine. The Holidays argument is optional, NOT
"required". Although it's use would seem to be of value if holidays should be
taken into account.

In any event, with your date in A1,

=WORKDAY(A1-DAY(A1),1)

will give you the first non-weekend day of the month in A1.

A list of holiday dates can be kept in a range of cells, and a reference to
that range optionally used in the Workday function.
--ron

I see that because I am used to seeing UK style dates I misread the OP as
asking for the first Monday of the month - I was wondering why all you guys
were getting it wrong <g

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
With 5/14/08 in H16 try:

=H16-DAY(H16)-WEEKDAY(H16-DAY(H16),2)+8

or:

=TODAY()-DAY(TODAY())-WEEKDAY(TODAY()-DAY(TODAY()),2)+8

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Needhelp!" wrote in message
...
Thank you for the response. But this formula does not account for
weekends. I
am looking for the first business day of each month.

Thanks again.


"FSt1" wrote:

hi
=DATE(YEAR(TODAY()),MONTH(TODAY()),1)
regards
FSt1

"Needhelp!" wrote:

Is there a funtion I can use to automatically pick up the first
business day
of the month based on a given date?

For instance, if today is 5/14/08, I would like a function to pull in
the
first business day of the month (5/1/08), based on 5/14/08.

I have tried using the Workday function, but this function requires
you to
put in the number of holidays you would like to exclude and doesn't
always
work out.