Workday function query

  • Thread starter Thread starter MrIainMacleod
  • Start date Start date
M

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
 
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)
 
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?
 
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?
 
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
Click to expand...

=WORKDAY(your_formula +1,-1)

--ron
 
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...
 
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)?
Click to expand...

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
 
Hi Ron

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

DOW *is* 5 in the formula I posted.
=A1+1-WEEKDAY(A1+1-DOW)
Click to expand...
where A1 contains your derived date, and DOW is the day of the week where
Sun=1.
Click to expand...
So, in your specific case:
Click to expand...
=your_formula+1-WEEKDAY(your_formula-4)
Click to expand...


or, expanded:

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

Note that with DOW = 5,

+ 1 - 5 = 4

--ron
 
Back
Top