+ Reply to Thread
Results 1 to 8 of 8

Days of the month and week based on name month/year

  1. #1
    Registered User
    Join Date
    08-30-2016
    Location
    Dallas, Texas
    MS-Off Ver
    excel 2013
    Posts
    71

    Days of the month and week based on name month/year

    Hi everyone,

    I'm new here and need some help. I'm trying to figure out how to auto populate cells in 2 rows. the first row would be the day of month (1-31) and the row beneath would be day of the week (Mon - Fri). These cells would populate after entering the month/year in a different cell. I'm trying to do this w/out building a table of the days of the month and days of the week. Any suggestions would be greatly appreciated. Thank you.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Days of the month and week based on name month/year

    If you put 9/2016 in A1, formatted as mm/yyyy, Excel will treat it as a date. If you put =A1 in some other cell and format it as dd, Excel will display 01. If in another cell you put =A1 and format it as ddd, Excel will display Thu, if you format it as dddd it will display Thursday.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    08-30-2016
    Location
    Dallas, Texas
    MS-Off Ver
    excel 2013
    Posts
    71

    Re: Days of the month and week based on name month/year

    Thank you for replying. I think that returns a single day. if I am wrong about that, I apologize. What I need is the entire month. So if I enter January 2017, I'm trying to auto populate 2 rows in 31 columns with the top row reading 1 thru 31 and second row reading the corresponding day of the week for the day of the month.

  4. #4
    Registered User
    Join Date
    08-30-2016
    Location
    Dallas, Texas
    MS-Off Ver
    excel 2013
    Posts
    71

    Re: Days of the month and week based on name month/year

    I'm sorry, I should've already posted this. Here is how I am currently doing this:

    Cell A1 is where the month/year is entered
    Cells D2 - AH2 are manually numbered 1-31
    Days of the week are done using =TEXT($A$1,"ddd"), next cell is =TEXT($A$1+1,"ddd"), etc

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Days of the month and week based on name month/year

    A1 --> 03/2028, formatted as DD/YYYY
    D2 --> =A1, formatted as dd
    E2:AH2 --> =IFERROR(IF(MONTH(D$2+1)=MONTH($A$1),$A$1+COLUMN()-4,""),"")
    D3:AH3, --> =D2
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-30-2016
    Location
    Dallas, Texas
    MS-Off Ver
    excel 2013
    Posts
    71

    Re: Days of the month and week based on name month/year

    That is awesome! Thank you very much!

    If you have time, can you walk me through it please. I'm trying to understand what each part does.

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Days of the month and week based on name month/year

    If you type "03/2028" into A1, Excel interprets it as a date and you'll see 02/01/2028 in the formula bar, but it will display differently in the cell itself. On my machine I would see Mar-2028, so I formatted the cell as custom: mm/yyyy.

    in D2 entering the formula =A1, puts the date in A1 in D2. D2 is formatted as d to display just the day number of the month in the cell.

    In E2, I wanted to just add 1 to the previous day's date but I also have to check to see if that results in going over to the next month: does MONTH(D$2+1)=MONTH($A$1)? If the month is the same then get the column number (for example, for the formula in E2, the column is 5 so I subtracted 4 and added the 1 remainder to the date in A1 to get the second day. However, if the months are not the same as when going from the last day of February to March, the cell is left as blank. So the to catch the error in the cell which would have Feb 31st I used the IFERROR to show it as blank also.

    Note that the values in D1:AH2 are Excel dates formatted just to show the day of the month. So, the values in row 3 are those same dates but formatted to show the day names only.

  8. #8
    Registered User
    Join Date
    08-30-2016
    Location
    Dallas, Texas
    MS-Off Ver
    excel 2013
    Posts
    71

    Re: Days of the month and week based on name month/year

    Thank you for taking the time to help me out on this. I really appreciate it!

+ 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. [SOLVED] Formula for counting rain days of the year, month, and or week
    By dedark05 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 05-24-2016, 01:27 AM
  2. [SOLVED] Calculate Week Range from Week Number, Month and Year?
    By A108A108 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 05-07-2016, 03:11 PM
  3. [SOLVED] Determine work days in current month or next month based on day of the month
    By sbrnard in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-29-2014, 05:14 PM
  4. find time left in month.days unit between two dates(in YEAR.MONTH.DAY)
    By xcfeng95 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 02-21-2014, 12:55 PM
  5. Return value of the last week of the month based on the year and month
    By stevekho2 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-14-2013, 11:04 AM
  6. Replies: 12
    Last Post: 02-08-2013, 02:26 PM
  7. [SOLVED] Auto fill week days based on month chosen
    By mteeters in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-14-2012, 02:45 PM

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