+ Reply to Thread
Results 1 to 11 of 11

Strip day from date

  1. #1
    Registered User
    Join Date
    10-22-2013
    Location
    Utrecht, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    29

    Strip day from date

    In this thread I received an answer on how to strip the time from a date formated field using "=INT()". At the moment this works fine for me but I wonder if there is a possibility to strip the day form the datefield as well. This would be a great help in my monthly reports.
    Last edited by Armand0; 03-03-2014 at 09:04 AM. Reason: Inserted hyperlink to former thread. I initialy intended, but forgot to do so
    Cheers, Armand0

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

    Re: Strip day from date

    Maybe

    =TEXT(A1;"MMMM YYY")

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,129

    Re: Strip day from date

    hi Armand. if eg is:
    15 Apr 2014

    what do you want? 15? Tue? Tuesday? the answers in order are:
    =DAY(A1)
    =TEXT(A1,"ddd")
    =TEXT(A1,"dddd")

    for 2nd & 3rd one, you also simply format cells of A1 as Custom:
    ddd
    or:
    dddd

    with INT, it looks like:
    =MONTH(INT(A1))

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Registered User
    Join Date
    10-22-2013
    Location
    Utrecht, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Strip day from date

    Currently using INT() I see something like "19-2-2014 00:00" (format=d-m-yyyy) I would like it to be "2-2014" (m-yyyy)

    =MONTH(INT(A1)) makes the year show AS 1900

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Strip day from date

    Maybe this

    =TEXT(A1,"m-yyyy")
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,228

    Re: Strip day from date

    If this is for a pivot table you can group by month.
    If posting code please use code tags, see here.

  7. #7
    Registered User
    Join Date
    10-22-2013
    Location
    Utrecht, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Strip day from date

    The replies with "=TEXT(A1,"m-yyyy")" are not doing what I'm looking for.
    Since it is for a pivot table I'll try to find the group by month option as suggested by Norie

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Strip day from date

    Quote Originally Posted by Armand0 View Post
    The replies with "=TEXT(A1,"m-yyyy")" are not doing what I'm looking for.
    Since it is for a pivot table I'll try to find the group by month option as suggested by Norie
    Of course, to group dates by month you would need real dates for excel to recognize them. Any text interpretations of the date will not work.

  9. #9
    Registered User
    Join Date
    10-22-2013
    Location
    Utrecht, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Strip day from date

    In the pivot sheet I can't find an option to sort by month. The Date field is situated in "Feport Filter". Group selection does not work there.
    Last edited by Armand0; 03-04-2014 at 11:15 AM.

  10. #10
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Strip day from date

    In the PivotTable click on the Date field, go to Options located in the Ribbon, click Group Field and select Month.

  11. #11
    Registered User
    Join Date
    10-22-2013
    Location
    Utrecht, The Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Strip day from date

    Quote Originally Posted by AlKey View Post
    In the PivotTable click on the Date field, go to Options located in the Ribbon, click Group Field and select Month.
    Unfortunately this does not work. The cell shows "All" and a drop-down arrow. When the cell is selected the "Groupfield in the ribbon is greyed-out.

+ 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] Strip time from date
    By Armand0 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-03-2014, 04:11 AM
  2. Strip date and keep time from row of cells
    By Christians in forum Excel General
    Replies: 2
    Last Post: 04-13-2011, 02:50 PM
  3. Strip Date From Text String
    By jaslake in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-19-2010, 10:34 PM
  4. [SOLVED] How can you strip the time of day out of a date field
    By Ron in forum Excel General
    Replies: 3
    Last Post: 04-19-2006, 02:30 PM
  5. TAB STRIP
    By icebreaker914 in forum Excel General
    Replies: 1
    Last Post: 04-16-2005, 09:08 AM

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