+ Reply to Thread
Results 1 to 7 of 7

Calculate Date from Month, Year and Week #

  1. #1
    Registered User
    Join Date
    10-17-2020
    Location
    Boise, Idaho
    MS-Off Ver
    16
    Posts
    5

    Calculate Date from Month, Year and Week #

    Greetings Everyone,
    I need some help in figuring out if what i want is possible. What Iím attempting is a time sheet where in Column A it will show the Day # based of Month, Year and Week Number. Example A4 = 06/02/21

    Any Help or tips are appreciated!
    Attached Files Attached Files
    Last edited by Mr.Trouble; 06-08-2021 at 02:54 PM. Reason: Solved

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,151

    Re: Calculate Date from Month, Year and Week #

    Not sure I've interpreted correctly, but I'll start with this. With the understanding the days listed are in week 1 of June 2021 this in A3 and filled down.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Not sure how many formulas you will be using I created some array constants to eliminate redundant calculations.

    And BTW in A4 I get 6/1/2021 which is correct.


    A
    B
    C
    D
    E
    F
    G
    1
    Month
    June
    Year
    2021
    2
    Date
    Days
    Time
    in
    Lunch
    Out
    Lunch
    In
    Time
    Out
    Out of
    Office
    3
    6/7/2021
    Monday
    4
    6/1/2021
    Tuesday
    5
    6/2/2021
    Wednesday
    6
    6/3/2021
    Thursday
    7
    6/4/2021
    Friday
    Last edited by FlameRetired; 06-07-2021 at 07:41 PM.
    Dave

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,017

    Re: Calculate Date from Month, Year and Week #

    Or try:

    Please Login or Register  to view this content.

  4. #4
    Valued Forum Contributor wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    EXCEL 2007/365
    Posts
    1,093

    Re: Calculate Date from Month, Year and Week #

    Define the name, the name is VV, enter the formula as below
    =DATE($E$1,MATCH($B$1,XX,),1)

    Define the name, the name is XX, enter the formula as below
    ={"January";"February";"March";"April";"May";"June";"July";"August";"September";"October";"November";"December"}

    Define the name, the name is YY, enter the formula as below
    ={"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday";"Sunday"}

    Define the name, the name is ZZ, enter the formula as below
    =ROW(INDIRECT(VV&":"&DATE($E$1,MATCH($B$1,XX,),DAY(EOMONTH(VV,0)))))

    Cell A3 Array formula , drag down
    HTML Code: 
    =TEXT(INDEX(ZZ,SMALL(IF(WEEKDAY(ZZ,2)=MATCH($B3,YY,),ROW(INDIRECT("1:"&DAY(EOMONTH(VV,0)))),4^8),COUNTIF($B$3:$B3,$B3))),"YYYY/M/D")

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    12,151

    Re: Calculate Date from Month, Year and Week #

    Quote Originally Posted by Phuocam View Post
    Or try:

    Please Login or Register  to view this content.
    Very clever.

  6. #6
    Registered User
    Join Date
    10-17-2020
    Location
    Boise, Idaho
    MS-Off Ver
    16
    Posts
    5

    Re: Calculate Date from Month, Year and Week #

    Thank you Everyone for your help! with your help i was able to finish building my excel workbook. I've included a copy if you'd like to check it out.
    Attached Files Attached Files

  7. #7
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,017

    Re: Calculate Date from Month, Year and Week #

    Quote Originally Posted by FlameRetired View Post
    Very clever.
    Thank you for the added rep.

+ 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] 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
  2. VBA to calculate AVERAGEIF and SUMIF for current week/month/year
    By lostest in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-21-2016, 07:35 AM
  3. Calculate Start & End Date & Month for Each Week In Fiscal Year
    By sarndt01 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-22-2014, 02:14 PM
  4. Replies: 1
    Last Post: 12-15-2011, 05:32 AM
  5. [SOLVED] how to insert month date year and day of week
    By Sachi Noma in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 05-18-2006, 10:10 PM
  6. [SOLVED] how to insert month date year and day of week
    By Sachi Noma in forum Excel General
    Replies: 3
    Last Post: 05-18-2006, 10:10 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