Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
business | Excel Discussion (Misc queries) | |||
Business Day | Excel Discussion (Misc queries) | |||
WHERE CAN I GET A BUSINESS PROPOSAL OR A BUSINESS EXPENSE SHEET? | New Users to Excel | |||
Help keep incompetents in business | Excel Worksheet Functions | |||
DIV/0! and business application | Excel Worksheet Functions |