Last business day

[Hitchhiker42]

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?

 

[Sheeloo]

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

 

[T. Valko]

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)

 

[John C]

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)

 

[Hitchhiker42]

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?

 

[Rick Rothstein]

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

 

[John C]

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.

 

[Hitchhiker42]

Ok, Thanks, I'll try that!

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.