Jump to content


Photo

Why won't the format change for a date?

dates VLOOKUP TEXT VALUE custom formats inexact

  • Please log in to reply
7 replies to this topic

#1 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 09 September 2015 - 03:36 PM

I have a header row of dates. The dates are showing as mm/dd/yy, but I want them to be mm/dd.

 

So I select these cells and go to change the format in the Number Formats dialogue box. Yet, they do not change.

 

When a cell is selected the Number > Date value shows as a date, but refuses to change to just the month and day.

 

I've even tried removing all formatting, but to no avail.

 

What am I missing please?



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 09 September 2015 - 10:51 PM

This article has moved

 

We have moved this article to our new site. The answer to this question can now be found here:

 

https://thesmartmeth...nge-for-a-date/


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 10 September 2015 - 08:07 PM

Thanks, Jonathan.

 

I had forgotten about the VALUE function. So I tried that, and while I did see a change in the serial number, I still couldn't get my formula to work.

 

I've attached a sample file to demonstrate. Here's what I'm trying to achieve:

 

  • RSVP Outlook_Tracker (first tab) is a subset of people whose attendance I need to track. They belong to a larger group of people listed on the second tab: RSVPTracker.
  • On RSVPTracker (second tab), I manually update the cells under the dates listed at the right of the table. This shows whether they're attending or not or just haven't responded to my inquiry.

When I select a value in cell D5 on the first tab, that will change the dates for the rest of the week, which are shown in the cells to the right of it.

 

Those values impact the cells below them. These cells refer to the second tab, where I want to pull the values that intersect with the name and the date.

 

Attached File  PROTOTYPE.xlsm   159.56KB   163 downloads



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 11 September 2015 - 01:56 PM

Hi again phoenixrizing,

 

I have looked into it, and I can see that the date values on your second tab are definitely stored as text rather than as date serial numbers. This seems to be a side effect of the dates being headers within a Table.

 

Since you can't change the data type of Table headers, you'll have to use a text value for your lookup formula instead of a date serial number.

 

Your existing formula almost does this correctly, but it has a couple of minor errors. You've added an extraneous TEXT function and your lookup is searching for dates in the format m/dd, while the dates on the second worksheet are in the format m/dd/yy. After correcting these issues, everything seems to work correctly:

 

Attached File  PROTOTYPE.xlsx   145.69KB   163 downloads

PROTOTYPE.png

 

I hope this is what you're looking for, but please feel free to reply if it's still not quite what you need.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 15 September 2015 - 04:08 PM

Jonathan,
 
That's good to know about the header and dates. I was getting frustrated trying to figure out why the formats wouldn't change. Now I know. :)
 
Thanks for correcting the formula. Upon review it showed me two things that make it off-target (nothing to do with you).
 
1) You'll note that I gave Donald Duck a red dot on 5/31 and gave Clark Kent a green dot for the same day. Both are correct, but for some reason the cells to the right are also showing the same value and I don't understand why.
 
2) I should also take into account when a date in the header on RVSP Outlook_Tracker is not contained with the header row of the table on the RSVPTracker tab. Using another file as a test, I believe this can be solved using the IFERROR and having it substitute another bit of text to show nothing is scheduled for that day. Once I get #1 resolved, I'll implement this.
 
Any thoughts on what's happening with #1?
 
Attached File  PROTOTYPE_jonathan3.xlsx   146.63KB   131 downloads


#6 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 15 September 2015 - 07:01 PM

I just now noticed something odd in the file I uploaded in the previous entry. I redownloaded your file and it's happening there.

 

The issue is when you select an earlier date in cell D4 (4/4, 4/11) all of the cells with the formula go "N/A". The dates are included in the header row on the source tab, so I find it strange that it does this when all that's changed is the date.

 

???  :(



#7 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 887 posts

Posted 15 September 2015 - 10:02 PM

Hi phoenixrizing,

 

I have taken a look and there were two issues that were causing problems.

 

Firstly, your MATCH function was using inexact matching, so it wasn't always finding the correct column for each date. Setting its last argument to 0 tells it to use exact matching and solves this issue.

You can see more about exact versus inexact matching in our Expert Skills Books and E-books.

 

This seems to solve the first problem you noticed.

 

The second problem was that the formulas were returning #N/A after changing the date. The reason for this is my fault - in my version of the formulas I changed the TEXT function to this:

TEXT(D$4,"m/dd/yy")

 

This works perfectly for dates with two-digit days, but your RSVPTracker worksheet only has single digits for the days, meaning my formula was trying to match 4/04/16 with 4/4/16 and failing to find a match.

The correct formula looks like this:

TEXT(D$4,"m/d/yy")

 

Making this change seems to fix the formula so that it always matches the dates correctly.

 

I have attached another copy of the workbook with the changes applied:

 

Attached File  PROTOTYPE_jonathan3.xlsx   146.58KB   125 downloads

PROTOTYPE3.png


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#8 phoenixrizing

phoenixrizing

    Advanced Member

  • Members
  • PipPipPip
  • 122 posts

Posted 16 September 2015 - 04:22 PM

This is great! Thank you for your help Jonathan.







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users