+ Reply to Thread
Results 1 to 6 of 6

Need formula to change dates based on given year

  1. #1
    Registered User
    Join Date
    07-27-2013
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2011 OS X
    Posts
    4

    Need formula to change dates based on given year

    Hey guys. I am not too experienced with Excel. Took a class, I get the gist, but I'm still learning.

    This spreadsheet is used to schedule music leaders for my church.

    I would like to tell my worksheet what year it is and have all the dates change in each column. If there is not a 5th weekend in the month, I would like to show "-".

    In the first week, if either the Saturday or Sunday falls in the new month, I consider that week 1. That's why the first week in September reads "Oct 31 & Sep 1)

    I need the formatting to read "31 & 1"

    I have come mildly close to figuring this out, but am still having trouble. Can any one help me figure this out? It is a challenge I would love to work out.

    I've included the worksheet in case you would like to take a look.

    Thanks in advance.

    -Keith

    Screen Shot
    Setting Up Automatic Date Change.png

    Worksheet
    Worship Leader Scheduling-2013.xlsx
    Attached Images Attached Images
    Last edited by kesmith2; 07-27-2013 at 02:39 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Need formula to change dates based on given year

    See attached file...

    Assumed Jan' thru Aug' was following year (2014) because your sheet label was FY2013.

    Also updated your conditional formatting per your other thread (including modification of your "red" formula).

    Perhaps you don't learn as much with me doing this for you, but I have a tendency to get carried away Your learning exercise for the near future is to break down the formulas used and determine how they work

    PS: You can also buy me a dinner the next time I'm in the Nashville area
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Need formula to change dates based on given year

    Guess you'll have to hold off on the dinner...

    I just realized some of the 5th week day/dates are off...

    "I'LL BE BACK"

  4. #4
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Need formula to change dates based on given year

    Okay, I think I got it right this time...

    Dinner's back on
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Need formula to change dates based on given year

    Oops! Forgot to change formula in one cell... J1.

    Copy J10 and Paste to J1.

    Dinner's still on

  6. #6
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Need formula to change dates based on given year

    Here's an updated version which uses simpler formulas for 2nd thru 5th weekends...
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. I want the dates to change automatically so I can print out a year's worth.
    By MrHappyGoLucky12 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-04-2013, 09:02 PM
  2. VBA Code to detect change in fiscal year between dates
    By miam in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2011, 03:33 AM
  3. Based on col dates create col of dates for the first day of that same month and year
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-28-2011, 09:32 PM
  4. Excel 2007 : Count based on year in a row of dates
    By Dadoo in forum Excel General
    Replies: 10
    Last Post: 01-24-2011, 10:08 PM
  5. change dates to current year
    By hayden1616 in forum Excel General
    Replies: 5
    Last Post: 04-21-2009, 06:58 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1