+ Reply to Thread
Results 1 to 4 of 4

Convert days by week in a year function

  1. #1
    Forum Contributor
    Join Date
    06-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2019 Version1808
    Posts
    300

    Convert days by week in a year function

    Hi Experts,

    Data runs in over 10000K rows with dates in a sheet(Excel 2010)
    Need to convert them in a weeks as follows for year 2015 and 2016

    01Jan to 07 Jan is week 1
    08 Jan to 14 Jan is Week2
    15Jan to 22Jan is week3
    23Jan to 30Jan is Week4
    31Jan to 07 Feb Week 5... up to 24Dec to 31Dec is Week52

    Any helpful tips or suggestion for function is appreciated
    Karnik

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Convert days by week in a year function

    Your date calculations are wrong : 15th to 21st is week 3

    Try

    =INT((A1-$C$1-1)/7)+1

    C1=31/12/2014

  3. #3
    Forum Contributor
    Join Date
    06-24-2012
    Location
    Australia
    MS-Off Ver
    Excel 2019 Version1808
    Posts
    300

    Re: Convert days by week in a year function

    Hi John,
    Thanks for your valuable suggestion.
    Your solution has almost reached me to the shore,the result gives me in dates ; for all dates in between 1-7Jan 2015 gives 01-Jan-00
    I would like to have results as Week1 for all 1-7Jan dates
    Any further suggestion possible?
    Thanks
    Karnik

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Convert days by week in a year function

    Assuming data starts in A1
    try

    =INT((A1-DATE(YEAR(A1)-1,12,31)-1)/7)+1

+ 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] How do return days,week no,month and year in between two dates
    By silambarasan.J in forum Excel General
    Replies: 5
    Last Post: 10-26-2016, 04:29 AM
  2. [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
  3. [SOLVED] Using days of the week in a IF function
    By greaseebogus in forum Excel General
    Replies: 5
    Last Post: 06-26-2014, 07:47 PM
  4. Convert date to week number for fiscal year starting July 25
    By ARayburn in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-15-2013, 10:07 AM
  5. Separating days by Year/Week in a date range :)
    By scotinexcile in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-21-2012, 09:26 PM
  6. Convert a multiple year list of 365 days/year into an equivalent of 360 days/year
    By lobotomy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-24-2012, 05:39 PM
  7. formula/macro. How to convert data (weekday, week number, year) to a date?
    By rain4u in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-19-2011, 10:34 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