+ Reply to Thread
Results 1 to 4 of 4

Formula to return day based on month end date & day number of month

  1. #1
    Forum Contributor
    Join Date
    06-23-2005
    Posts
    253

    Formula to return day based on month end date & day number of month

    Need formulas for cells D4 thru AH4 that return the 1st 3 digits of the day of the week created from the month end date in cell AB2 & the day number of the month in cells E4 thru AH4.

    For example, with the date 10/31/07 in cell AB2

    ___D_|_E_|__F_|_G__|_H_|_I_|_J_|_K__|_L_|_M__|_N_ |_O |_P_|_Q_|,etc
    1
    2
    3
    4 Mon|Tue|Wed |Thur|Fri_|Sat|Sun|Mon|Tue|Wed |Thur|Fri_|Sat|Sun|, etc
    5__1_|_2_|__3_|__4_|_5_|_6_|_7_|_8_ |_9_|_10_|_11_|12_|13_|14_|,etc

    Formula in cell D4 returns Mon because thats the day of 10/01/07
    Formula in cell E4 returns Tue because thats the day of 10/02/07
    Formula in cell F4 returns Wed because thats the day of 10/03/07
    etc

    Thanks for all your help. mikeburg

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Try in D4 copied across

    =TEXT(DATE(YEAR($AB$2),MONTH($AB$2),COLUMN(A1)),"ddd")
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    An alternate way and more robust...

    Try in D4:

    =DATE(YEAR($AB$2),MONTH($AB$2),COLUMN(B1)-COLUMN($A$1)+1)

    and in E4 copied across as far as you want....

    =IF(MONTH(D4+1)<>MONTH($AB$2),"",DATE(YEAR($AB$2),MONTH($AB$2),COLUMN(B1)-COLUMN($A$1)+1))

    and format all cell as Custom: ddd

    this will leave cells after the last day of the month blank.

    You can then copy those exact formulas down to row 5 and format the row 5 cells as Custom: d to get numbers.
    Last edited by NBVC; 09-21-2007 at 10:38 AM.

  4. #4
    Forum Contributor
    Join Date
    06-23-2005
    Posts
    253
    Thank you so very, very much! mikeburg

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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