Workday function query

[MrIainMacleod]

Hi there.

I'm trying to find a way of forcing the output of a formula to be a
workday.

i.e. If the outcome of the formula is a Saturday or Sunday, to deliver
the prior Friday's date

I know I could add an IF statement, but the formula is messy enough as
it is, and I'd rather not confuse the poor souls who will inherit the
spreadsheet from me.

Many thanks

 

[Roger Govier]

Hi

The following will add -1 day and -2 days respectively, to the date derived
from your calculation
=your_calc+CHOOSE(WEEKDAY(your_calc,2),0,0,0,0,0,-1,-2)

 

[iainmacleod]

Many thanks for the imput.

The current formula is as follows:

=IF(ISBLANK(F13),"",IF(J13="Yes",DATE(YEAR(WORKDAY(AP13,-P13-1)),
MONTH(WORKDAY(AP13,-P13-1))-A335, DAY(WORKDAY(AP13,-P13-1))
+0),WORKDAY(AP13,-P13-1)))

Where would I add the elements you've suggested?

 

[iainmacleod]

Many thanks for the input.

I'm having trouble getting the elements you've suggested to 'fit'
within my existing formula.

It looks like this at the moment:

=IF(ISBLANK(F13),"",IF(J13="Yes",DATE(YEAR(WORKDAY(AP13,-P13-1)),
MONTH(WORKDAY(AP13,-P13-1))-A335, DAY(WORKDAY(AP13,-P13-1))
+0),WORKDAY(AP13,-P13-1)))

Any suggestions?

 

[Ron Rosenfeld]

Hi there.

I'm trying to find a way of forcing the output of a formula to be a
workday.

i.e. If the outcome of the formula is a Saturday or Sunday, to deliver
the prior Friday's date

I know I could add an IF statement, but the formula is messy enough as
it is, and I'd rather not confuse the poor souls who will inherit the
spreadsheet from me.

Many thanks

=WORKDAY(your_formula +1,-1)

--ron

 

[iainmacleod]

Brilliant!

Many thanks.

 

[Ron Rosenfeld]

Brilliant!

Many thanks.

You're welcome. Glad to help. Thanks for the feedback.
--ron

 

[Roger Govier]

Hi Ron

that's smart!!

--
Regards
Roger Govier

=WORKDAY(your_formula +1,-1)

--ron

 

[Ron Rosenfeld]

Hi Ron

that's smart!!

Thank you. Of course, you have to have the ATP functions.
--ron

 

[iainmacleod]

OK, another one building on this issue...

How do I 'force' a formula to return a date for a given day of the
week.

i.e. I am trying to map a process (to return dates for each milestone
based on start or end date) where a given step must take place on a
Thursday.

How do I come up with a formula which returns the Thursday prior, to a
calculated date (if the actual derived date is not a Thursday)?

Trying to make this make sense, without writing a whole essay, but
failing miserably...

 

[Ron Rosenfeld]

How do I come up with a formula which returns the Thursday prior, to a
calculated date (if the actual derived date is not a Thursday)?

In general:

=A1+1-WEEKDAY(A1+1-DOW)

where A1 contains your derived date, and DOW is the day of the week where
Sun=1.

So, in your specific case:

=your_formula+1-WEEKDAY(your_formula-4)
--ron

 

[Roger Govier]

Hi Ron

If you are working on 1=Sunday, then shouldn't DOW for the OP be 5, not 4?

 

[Ron Rosenfeld]

Hi Ron

If you are working on 1=Sunday, then shouldn't DOW for the OP be 5, not 4?

DOW *is* 5 in the formula I posted.

=A1+1-WEEKDAY(A1+1-DOW)

where A1 contains your derived date, and DOW is the day of the week where
Sun=1.

So, in your specific case:

=your_formula+1-WEEKDAY(your_formula-4)

or, expanded:

=your_formula+1-WEEKDAY(your_formula+1-DOW)

Note that with DOW = 5,

+ 1 - 5 = 4

--ron

 

[Roger Govier]

Sorry Ron, I was being even more dense than usual!!!