Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Is there any Excel functions that can populate the last 3 weekdays in 3 cells?

Like:

A1=7/9/2012
A2=7/6/2012
A3=7/5/2012
share|improve this question

1 Answer 1

up vote 4 down vote accepted

Method #1

You could try these formulas in 3 cells:

=WORKDAY(TODAY(),-1)

=WORKDAY(TODAY(),-2)

=WORKDAY(TODAY(),-3)

Method #2

This formula will return the last nTH weekday/workday.

=TODAY()-CHOOSE(WEEKDAY(TODAY()),2,3,n,n,n,n,n)

Examples:

=TODAY()-CHOOSE(WEEKDAY(TODAY()),2,3,1,1,1,1,1)

=TODAY()-CHOOSE(WEEKDAY(TODAY()),2,3,2,2,2,2,2)

=TODAY()-CHOOSE(WEEKDAY(TODAY()),2,3,3,3,3,3,3)

--

If TODAY() were 7/09/2012 (a Monday), the first formula for both methods would return 7/6/2012 (a Friday).

To see the days of the week along with the dates, try the following number formats:

  • ddd mm/dd/yyyy
  • dddd mmmm d, yyyy
  • dddd
share|improve this answer
1  
I did this whole work around using ifs and weekday function and forgot about workday. :/ Tested and worked. –  Cameron Aziz Jul 13 '12 at 7:01
    
@CameronAziz It's also possible with the weekday() function. –  Ellesa Jul 13 '12 at 7:06
1  
ya, I did it quick and dirty: =IF(WEEKDAY((TODAY()-1),1)=6, (TODAY()-2), IF(WEEKDAY((TODAY()-1),1)=7, (TODAY()-3), (TODAY()-1))) –  Cameron Aziz Jul 13 '12 at 14:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.