+ Reply to Thread
Results 1 to 13 of 13

Auto Fill not working with date formating

  1. #1
    Registered User
    Join Date
    12-18-2019
    Location
    Edmonton, Canada
    MS-Off Ver
    2016
    Posts
    6

    Auto Fill not working with date formating

    My auto fill isn't working right. When I put in the cell formatting for the dates to display the way I like, then the auto fill displays wrong I have included a Screen shot Capture.PNG
    Attached Files Attached Files
    Last edited by Nicole parent; 12-18-2019 at 04:42 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,415

    Re: Auto Fill not working with date formating

    Select all the cells then right-click and choose Format Cells, then choose the format you require on the Number tab (e.g. from the Date option).

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    12-18-2019
    Location
    Edmonton, Canada
    MS-Off Ver
    2016
    Posts
    6

    Re: Auto Fill not working with date formating

    I did that and now if you look at the top the cell has the right date in it but it displays as the wrong date

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,415

    Re: Auto Fill not working with date formating

    I can only see what is in a single cell (A3). I don't know if the other cells showing December-19 contain the same date or different dates, but I suspect they increase by 1 day up to 31st December, then change to 01/01/2020 and so on.

    If straight formatting does not change them to the style you prefer, then you may have Conditional Formatting applied to those cells, as that takes priority over normal formatting.

    It would help if you attached a sample Excel workbook, rather than a picture of one. The yellow banner at the top of the screen gives details of how you can do this.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    12-18-2019
    Location
    Edmonton, Canada
    MS-Off Ver
    2016
    Posts
    6

    Re: Auto Fill not working with date formating

    Yeah Its not that I have looked at that too but I did attach the work book

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,415

    Re: Auto Fill not working with date formating

    I opened your workbook. The offending cells were already selected, so I clicked on Conditional Formatting | Manage Rules and discovered that there is no conditional formatting set up. So I right-clicked and selected Format Cells, and when the dialogue box opened it showed that the format on the Number tab for those cells was Date | March-01 (i.e. as mmmm-yy), so I selected a different format (3/14/01, which is the same as m/d/yy) and after clicking OK the display changed instantly.

    When I added a new date at the bottom it again showed as January-20, so really the whole of that column has been pre-formatted as mmmm-yy. You should select all the cells in that column by clicking on the column identifying letter at the top, and then apply the format that you require. Don't forget to click OK when you are done.

    Hope this helps.

    Pete

  7. #7
    Registered User
    Join Date
    12-18-2019
    Location
    Edmonton, Canada
    MS-Off Ver
    2016
    Posts
    6

    Re: Auto Fill not working with date formating

    Thank you I did Know I could change the formatting to a different one and have it work out but I need it in the formatting it was in. It has to be mm-yyyy do you think there is any way to make excel not mess it up in that format?

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,415

    Re: Auto Fill not working with date formating

    I don't really know what you are getting at. Is the format as shown in the first screen shot wrong? If so, how would you like it to appear? If you use a format of mm-yyyy this will display as 12-2019 for any date in December 2019, and as 01-2020 for any date in January 2020. Is that what you want?

    Pete

  9. #9
    Registered User
    Join Date
    12-18-2019
    Location
    Edmonton, Canada
    MS-Off Ver
    2016
    Posts
    6

    Re: Auto Fill not working with date formating

    I want it to show in the cell as December-19 then December-20 and so on. But when I select that formatting the workbook displays like the one I attached above. I want to know if there is a way to use that formatting but still have the auto fill display correctly

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,415

    Re: Auto Fill not working with date formating

    Is 19 the day of the month or the year?

    Assuming it is the day of the month, then select all the cells, then right-click and select Format cells, and on the Number tab you should select Custom (at the bottom of the list). Then instead of the mmmm-yy that shows, you should use mmmm-dd, then click on OK.

    Hope this helps.

    Pete

  11. #11
    Registered User
    Join Date
    12-18-2019
    Location
    Edmonton, Canada
    MS-Off Ver
    2016
    Posts
    6

    Re: Auto Fill not working with date formating

    I didn't even think that it was displaying in mmmm-yy. Thank you so much, So simple but sometimes it just takes another set of eyes. Thank you very much

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,415

    Re: Auto Fill not working with date formating

    Yes!! We finally got there.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

  13. #13
    Registered User
    Join Date
    06-28-2016
    Location
    andheri
    MS-Off Ver
    2013
    Posts
    3

    Re: Auto Fill not working with date formating

    Attachment 655558

    This is jpeg file with screen if you any query connect to forum again Answer by Shirish Tiwari

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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