+ Reply to Thread
Results 1 to 4 of 4

Trying to Concatenate dates into sortable values with ISO week and Year. ie: 2019 10

  1. #1
    Registered User
    Join Date
    04-25-2015
    Location
    Ontario, Canada
    MS-Off Ver
    2016
    Posts
    72

    Trying to Concatenate dates into sortable values with ISO week and Year. ie: 2019 10

    Our business uses ISO weeks for everything, so I am trying to take date data out of SAP and make it usable in calculations in an ISO week format.

    Currently I am doing this:

    Cell 1
    =ISOWEEKNUM(cellwithdate)
    Cell 2
    =Year(cellwithdate)
    Cell 3
    =Concatenate(Cell 2," ",Cell 1)
    Result:

    2019 9

    Problem is what Excel recognizes 2019 9 as larger than 2019 16.

    Is there a better way to do this? We have orders being analyzed from current year, previous year and next year. I believe it needs the space or else it will think 20193 is smaller than 201020 (week 3 2019 vs week 20 2010)

    This is the supplied date format by the way:

    2019-10-25
    Attached Files Attached Files
    Last edited by dbl_dbl; 04-18-2019 at 02:01 PM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Trying to Concatenate dates into sortable values with ISO week and Year. ie: 2019 10

    =Cell 2 & text(Cell 1, " 00")
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    04-25-2015
    Location
    Ontario, Canada
    MS-Off Ver
    2016
    Posts
    72

    Re: Trying to Concatenate dates into sortable values with ISO week and Year. ie: 2019 10

    You the best! I completely forgot about the text function. Poor text function, so often forgotten.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Trying to Concatenate dates into sortable values with ISO week and Year. ie: 2019 10

    You're welcome.

+ 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 number for financial year dates
    By silambarasan.J in forum Excel General
    Replies: 27
    Last Post: 06-16-2023, 01:13 AM
  2. Calculate week number for Fiscal Year Dates
    By katie_10042 in forum Excel General
    Replies: 16
    Last Post: 02-14-2017, 05:58 AM
  3. Concatenate textbox and label values inside UF
    By Grilleman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-16-2015, 04:53 PM
  4. [SOLVED] Formula/Macro to list down the dates that are inside a week number of a month
    By michaljireht in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-22-2014, 08:16 AM
  5. [SOLVED] Trying to return the week number of dates in a fiscal year
    By NedFlanders in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-03-2013, 09:16 PM
  6. Calculate week number for Fiscal Year Dates
    By Ginu in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-16-2013, 03:10 PM
  7. Replies: 7
    Last Post: 11-12-2008, 04:13 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