Jump to content


Photo

Dynamically changing the incremental value to generate Friday dates

WEEKDAY DATE formula palette

  • Please log in to reply
2 replies to this topic

#1 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 06 July 2016 - 12:03 AM

G6 takes a start date (in C27) and calculates to a Friday. I then want each cell to the right of G6 to auto-populate with the next Friday.
 
The goal is to have those dates automatically reflect the appropriate dates for consecutive Fridays, no matter what start date is entered in C27.
 
Note: Even though April 2016 has a Friday that starts on April 1, I purposely had G6 start on April 8 because I will eventually calculate for hours leading up to April 8.
 
In rows 1, 2 and 3, I attempted to create other formulas to be used as counters to help me add the appropriate number of days to row 6 so all that I would need to do would be copy and drag G6 to however far I need it to go in that row. Alas, I couldn't make them work for me.
 
In summary: how can I tell each cell from G6 onward to reflect only Fridays based on the start date I enter in C27? I also need G5 and K5 to update the appropriate months. I did this by adding 1 to K5, but when I add more months, I'd like to have the user avoid entering the incremental value to make it work.
 
Attached File  generating fridays.xlsx   16.4KB   58 downloads


#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 862 posts

Posted 06 July 2016 - 12:41 PM

Hi phoenixrizing,

 

I can see you've attempted some very complex formulas to work this out but, as is often the case, Excel offers a function that will make this much easier: the WEEKDAY function.

 

WEEKDAY returns a number that corresponds to the day of the week of the provided date. By default, it treats Sunday at the first day of the week, but you can change this using its second argument (although that won't be necessary in this case).

 

Here's how the formula to get the date of the next Friday from any date looks:

=C27+(7-WEEKDAY(C27+1))

 

It might not be immediately obvious why this works, so here's an example using today's date: the 6th of July 2016. The date of the next Friday is the 8th of July - 2 days from now.

 

The formula calculates the WEEKDAY of today's date, returning 4 and adds 1 to this to get 5, then subtracts this from 7 to get 2.

Finally, the formula adds 2 days from the 6th of July, giving a final result of the 8th of July.

 

The reason this works is because Friday is week day 6 according to the WEEKDAY function. This means that on a Monday the formula will calculate 7-3=4 and add 4 days to the date, on a Tuesday it will be 7-4=3, etc.

 

For more help on the WEEKDAY function, you can use the help in Excel's formula palette.  For instructions on the formula palette, see the video and PDF lesson: Expert Skills Lesson 3-3: Use the formula palette and the PMT function.

 

Once the first Friday has been calculated with this formula, you can easily find all subsequent Fridays by simply adding 7 days to the date. These formulas can then be very easily filled across as many times as are needed.

 

You seem to have managed to extract the months without difficulty, so I'm not sure I really understand the problem with them. You might consider changing them to reference one of the dates below each 'month heading', but that probably won't make it any easier for your users to work with.

 

There's more about calculating months from dates in Excel in the video lesson: Expert Skills Lesson 3-11 Use the Excel DATE function to offset days, months and years.

 

I have attached a copy of your workbook with the new WEEKDAY function applied:

 

Attached File  generating fridays.xlsx   16.07KB   57 downloads

generating fridays.png

 

I hope this is the solution you were looking for, but as usual feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 07 July 2016 - 12:41 AM

This is great, Jonathan, and I think much more efficient.

 

Thanks!







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users