Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Date Based on Other Dates

1. ## Date Based on Other Dates

In A8 I have a number 1 - 5.

This identifies the weeks in a month 1 - 4 for four week months. The 5 is for a five week accounting month.

In A9 I have a day of the week it could be any day formatted as dddd

In A11 I have the 1st day of the month 01/01/2007 formatted as ddd/yyyy

I need a formulae that will look at the number in A8 i.e. 2
Then look at the day in A9 i.e. Tuesday
Then look at the month in A11 i.e. January

Based on all of that I want it to return the date of the 2nd Tuesday in January 2007 in B11.

In a five week month I need it to return the last Tuesday of the month.

Weeks per month are show in starting in D11 for January.

Any help appreciated.

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Date Formula For Specific Days

Not tested, but try,

=(A11-WEEKDAY(A11)+1+((A8-1)*7)+2)

Excel Dates & Times
Last edited by Dave Hawley; December 16th, 2006 at 10:11.

3. ## Re: Date Formula For Specific Days

Hi Kris,

Thanks for helping on this.

Your formula returns Tue 26/12/06 not the 2nd Tuesday of January 2007, and if I change B8 to 1 and B9 to Friday the date doesn't change to the first Friday of January 2007.

Tim.

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## Re: Date Formula For Specific Days

Try,

=(A11-WEEKDAY(A11)+1+((A8-1)*7)+VLOOKUP(A9,{"Sunday",1;"Monday",2;"Tuesday",3;"Wednesday",4;"Thursday",5;"Friday",6;"Saturday",7},2,0)-1)

HTH

5. ## Re: Date Formula For Specific Days

Timbo,

In a five week month I need it to return the last Tuesday of the month.
Do you mean that if there are 5 weeks in the month, you want the formula to ignore the value entered in A8 and always return the last occurrence of the selected day in that particular month?

6. ## Re: Date Formula For Specific Days

Hi Kris,

I just get #N/A

Tim.

Excel Video Tutorials / Excel Dashboards Reports

7. ## Re: Date Formula For Specific Days

It returns the correct date for me.

8. ## Re: Date Formula For Specific Days

Hi Batman,

Yes, whichever day of the week it is, if there are five of them in the month then show the date of the last Monday or Tuesday dependent on cells A8 and A9.

Tim.

Excel Video Tutorials / Excel Dashboards Reports

9. ## Re: Date Formula For Specific Days

See attachment, I have removed most of the worksheet to enable me to upload it.
Last edited by Timbo; December 15th, 2006 at 21:22.

Excel Video Tutorials / Excel Dashboards Reports

10. ## Re: Date Formula For Specific Days

But A8 holds the week number that you want to return, e.g. 2 tells the formula that you want to return the second Tuesday in the month. If A8 holds 2 and the month has 5 weeks, do you want to return the second Tuesday in the month or the last Tuesday in the month (which would effectively mean ignoring A8 if there were 5 weeks in the month)?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno