Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Workday (use preceeding workday)

Posted on 2014-11-07
6
Medium Priority
?
143 Views
Last Modified: 2014-11-07
Hello,

What do I need to change in the below to return the workday before and not the next workday?  I could need 2 days before and not only 1 day.  I dont know if it's as simple as changing a sign on the -1 and if you want 2 days before then change the 1 to a 2.

Here is the formula with an explanation from another expert (Barry Houdini)
=WORKDAY(EDATE(I34,CHOOSE(MATCH(D35,{"annually","quarterly","semi-annually","monthly"},0),12,3,6,1))-1,1)

<When the original formula gives you a weekend date that will jump to the next working day

Thank you.
0
Comment
Question by:pdvsa
  • 3
  • 2
6 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40428258
=WORKDAY(EDATE(I34,CHOOSE(MATCH(D35,{"annually","quarterly","semi-annually","monthly"},0),12,3,6,1))+1,-1)
0
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 40428267
Hi,

it should be

=WORKDAY(EDATE(I34,CHOOSE(MATCH(D35,{"annually","quarterly","semi-annually","monthly"},0),12,3,6,1))-1,-1)

Regards
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40428270
Rgonzo, that will change a Friday to a Wednesday. My code will keep a Friday to a Friday.

I'm assuming you want Sunday -> Friday, Saturday ->Friday, Friday ->Friday, Thursday->Thurday.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:pdvsa
ID: 40428312
Philip, yes.  And what if it's 2 days before?  Please bold what is edited
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40428319
Depends on what you mean.

If it's two working days before:

=WORKDAY(EDATE(I34,CHOOSE(MATCH(D35,{"annually","quarterly","semi-annually","monthly"},0),12,3,6,1))+0,-2)

This results in:

Mon      Thu
Tue      Fri
Wed      Mon
Thu      Tue
Fri      Wed
Sat      Thu
Sun      Thu

If it's two calendar days before, and then the previous working day, then

=WORKDAY(EDATE(I34,CHOOSE(MATCH(D35,{"annually","quarterly","semi-annually","monthly"},0),12,3,6,1))-1,-1)

This results in:
Mon      Fri
Tue      Fri
Wed      Mon
Thu      Tue
Fri      Wed
Sat      Thu
Sun      Fri
0
 

Author Closing Comment

by:pdvsa
ID: 40428451
thank you once again....
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question