Solved

Workday (use preceeding workday)

Posted on 2014-11-07
6
131 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 51

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
Industry Leaders: 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 500 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

707 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