Workday formula I\'m really struggling with workdays. I have a date in cell A14

Question

Workday formula

I'm really struggling with workdays.

I have a date in cell A14 which is 5/8/14. I want to have a formula which returns the last Monday but returns the workday before if it's a bank holiday.

I have the formula:

=WORKDAY(EOMONTH(A14,0)-WEEKDAY(EOMONTH(A14,0)+VLOOKUP($D$13,$P$14:$Q$18,2,0))+1,0,Table1[Date])

where Table 1 is a list of bank holiday dates which includes 25/8/14 and 22/8/14.

The formula returns 25/8/14 whereas I'm wishfully hoping that it would return 21/8/14 as this is the previous bank holiday.

After spending quite a few hours searching I can't find a) why it's not ignoring 25/8/14 and b) why it's not putting 21/8/14.

Can anyone help please?

It would be much appreciated!

Explanation / Answer

..A formula might work best:"

=WORKDAY(A14+2-WEEKDAY(A14-1),-1,BankHolidays[Dates])

A14+2-WEEKDAY(A14-1) returns the Tuesday preceding the date in A14

The WORKDAY formula then subtracts one (1) workday from that date, and uses the Table (in my version I renamed the table from Table1 to BankHolidays) for the optional Holidays argument..@

With A14: 25/8/14 the formula returns 21/8/14

However, if what you really want, when you write "Last Monday", is the Last Monday in the month or the preceding workday if the Last monday in the month is not a workday, substitute eomonth(a14,0) for a14 in the above:

=WORKDAY(EOMONTH(A14,0)+2-WEEKDAY(EOMONTH(A14,0)-1),-1,BankHolidays[Dates])

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.