+ Reply to Thread
Results 1 to 8 of 8

Formula to Return Week Number of Date

  1. #1
    Registered User
    Join Date
    04-04-2015
    Location
    Philadelphia, Pa
    MS-Off Ver
    Mac 2011
    Posts
    5

    Formula to Return Week Number of Date

    Can someone point me in the right direction to create two formulas. The first formula I am trying to create is having Excel return the week number that corresponds to the month. For example, I want April 4th to display in a column as the 5th week of March.

    The 2nd formula I am need help with is returning the month that corresponds to the calendar month. April 4th 2015 is part of March on the calendar if a new month always starts on a Sunday. Instead of April 4th showing in a Column as April, I want it to read March. Starting on Sunday April 5th, it would begin the Calendar month of April.

    Any help would be greatly appreciated.

    Thanks!
    Attached Files Attached Files
    Last edited by tdonnelly0987; 04-04-2015 at 12:52 PM.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Formula to Return Week Number of Date

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    Quang PT

  3. #3
    Registered User
    Join Date
    04-04-2015
    Location
    Philadelphia, Pa
    MS-Off Ver
    Mac 2011
    Posts
    5

    Re: Formula to Return Week Number of Date

    I attached a workbook with what I am trying to accomplish.

  4. #4
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Formula to Return Week Number of Date

    Please take a look at the file... hope the best

    cheers
    Attached Files Attached Files

  5. #5
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: Formula to Return Week Number of Date

    B2=TEXT(A2-WEEKDAY(A2,2)+1,"MMMM")
    C2=INT(DAY(A2-WEEKDAY(A2,2))/7)+1
    can try this too
    Last edited by samba_ravi; 04-04-2015 at 03:22 PM.
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  6. #6
    Registered User
    Join Date
    04-04-2015
    Location
    Philadelphia, Pa
    MS-Off Ver
    Mac 2011
    Posts
    5

    Re: Formula to Return Week Number of Date

    Quote Originally Posted by azumi View Post
    Please take a look at the file... hope the best

    cheers
    Thanks this was exactly what I was looking for. The only issue is Monday starts the new month instead of Sunday. 4/5 is still showing as March but 4/6 changes to April. Other than that this is perfect. Thank you!

  7. #7
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Formula to Return Week Number of Date

    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Formula to Return Week Number of Date

    Assuming Sunday week start you can make a small change to azumi's sugested formula, i.e.

    =INT((6+DAY(A2-WEEKDAY(A2)+1))/7)

    and to get the month on the same basis

    =TEXT(A2-WEEKDAY(A2)+1,"mmmm")
    Audere est facere

+ 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] Find week days within the date range and return number
    By CaineSmith in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-06-2014, 03:47 PM
  2. [SOLVED] require a formula to return the week number
    By john dalton in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-17-2014, 09:51 AM
  3. [SOLVED] Date Formula to Return Week Number in Current Month based on Reporting Period
    By jeversf in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-25-2013, 12:10 PM
  4. [SOLVED] Formula to return the week end day/date.
    By avidcat in forum Excel General
    Replies: 2
    Last Post: 09-14-2012, 05:19 PM
  5. Date Function formula that will return the date of a specific week
    By Greg in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-12-2006, 12: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