Last business day
 

Is there a function that can automatically insert the last business day? I
have one set up so that it does the previous day, but I have to change it
every monday so that it's last fridays date instead of sunday's. is there a
way to make that automatic?

Last business day
 

Try
=DATE(YEAR(A1),MONTH(A1)+1,0)-(MAX(0,WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,0),2)-5))

"Hitchhiker42" wrote:

Is there a function that can automatically insert the last business day? I
have one set up so that it does the previous day, but I have to change it
every monday so that it's last fridays date instead of sunday's. is there a
way to make that automatic?

Last business day
 

I have one set up so that it does the previous day

So you want yesterday's weekday date?

This requires the analysis ToolPak add-in be installed for Excel versions
prior to Excel 2007:

=WORKDAY(NOW(),-1)

--
Biff
Microsoft Excel MVP


"Hitchhiker42" wrote in message
...
Is there a function that can automatically insert the last business day? I
have one set up so that it does the previous day, but I have to change it
every monday so that it's last fridays date instead of sunday's. is there
a
way to make that automatic?

Last business day
 

Are you just looking for a formula?
If so, then
=WORKDAY(TODAY(),-1)
You could even modify it for holidays, if you have a list of holidays
somewhere like so:
=WORKDAY(TODAY(),-1,holidays)
--
** John C **

"Hitchhiker42" wrote:

Is there a function that can automatically insert the last business day? I
have one set up so that it does the previous day, but I have to change it
every monday so that it's last fridays date instead of sunday's. is there a
way to make that automatic?

Last business day
 

Thanks so much! I need to learn to do these secret voodoo formulas myself ^_^
If I were to do the holidays thing where would I create that list?

"John C" wrote:

Are you just looking for a formula?
If so, then
=WORKDAY(TODAY(),-1)
You could even modify it for holidays, if you have a list of holidays
somewhere like so:
=WORKDAY(TODAY(),-1,holidays)
--
** John C **

"Hitchhiker42" wrote:

Is there a function that can automatically insert the last business day? I
have one set up so that it does the previous day, but I have to change it
every monday so that it's last fridays date instead of sunday's. is there a
way to make that automatic?

Last business day
 

You question is not entirely clear. I guessing you want to see the previous
Friday even if today is Friday. If so, try this...

=A1-WEEKDAY(A1)-1

--
Rick (MVP - Excel)


"Hitchhiker42" wrote in message
...
Is there a function that can automatically insert the last business day? I
have one set up so that it does the previous day, but I have to change it
every monday so that it's last fridays date instead of sunday's. is there
a
way to make that automatic?

Last business day
 

What I usually do may be completely different than what others do. I can tell
you what I do though.
When I have larger workbooks, I usually have at least 1 tab that is
dedicated to tables, variables (that I would like to see), charts, etc.
Somewhere on here, select a range of cells, and I would name the selection
Holidays. Then in this selection of cells, enter the dates of Holidays (i.e.:
11/20/08, 12/24/08, 12/25/08, etc...). Then use the formula that I gave, and
it will ensure to 'skip' holidays that happen on a weekday.
--
** John C **


"Hitchhiker42" wrote:

Thanks so much! I need to learn to do these secret voodoo formulas myself ^_^
If I were to do the holidays thing where would I create that list?

"John C" wrote:

Are you just looking for a formula?
If so, then
=WORKDAY(TODAY(),-1)
You could even modify it for holidays, if you have a list of holidays
somewhere like so:
=WORKDAY(TODAY(),-1,holidays)
--
** John C **

"Hitchhiker42" wrote:

Is there a function that can automatically insert the last business day? I
have one set up so that it does the previous day, but I have to change it
every monday so that it's last fridays date instead of sunday's. is there a
way to make that automatic?

Last business day
 

Ok, Thanks, I'll try that!

"John C" wrote:

What I usually do may be completely different than what others do. I can tell
you what I do though.
When I have larger workbooks, I usually have at least 1 tab that is
dedicated to tables, variables (that I would like to see), charts, etc.
Somewhere on here, select a range of cells, and I would name the selection
Holidays. Then in this selection of cells, enter the dates of Holidays (i.e.:
11/20/08, 12/24/08, 12/25/08, etc...). Then use the formula that I gave, and
it will ensure to 'skip' holidays that happen on a weekday.
--
** John C **


"Hitchhiker42" wrote:

Thanks so much! I need to learn to do these secret voodoo formulas myself ^_^
If I were to do the holidays thing where would I create that list?

"John C" wrote:

Are you just looking for a formula?
If so, then
=WORKDAY(TODAY(),-1)
You could even modify it for holidays, if you have a list of holidays
somewhere like so:
=WORKDAY(TODAY(),-1,holidays)
--
** John C **

"Hitchhiker42" wrote:

Is there a function that can automatically insert the last business day? I
have one set up so that it does the previous day, but I have to change it
every monday so that it's last fridays date instead of sunday's. is there a
way to make that automatic?