Auto-Created Date field not displaying correctly.

**JonathanMoore** · 09-19-2011, 04:03 AM

Hello all,

I have a Cell that automatically works out and displays the date for the 1st sunday in the month depending on the date in cell B1:

Please Login or Register  to view this content.

This works like a dream and displays the date in the format i'm after which is:

yyyymmdd hhmm

Now, my main issue is when I try and add the date from the above formula into another (very long) formula. I've attached the code below, but the problem is when I use a cell reference to insert this date, it displays a random number (40790). I've changed the format of the cell containing the below formula to a date format, but it's making no difference

Please Login or Register  to view this content.

The Cell reference that I am talking about is the one that looks like this: ", Diesel!$B$2,".

Does anyone have any ideas as to why the date is being shown perfectly in one cell, but whenever I try and reference it somewhere else it is shown as a random number?

Thanks in advance

- Jon

**Andrew-R** · 09-19-2011, 04:30 AM

You can force the reference to Diesel!$B$2 into the format that you want using ... TEXT(Diesel!$B$2,"yyyymmdd hhmm")

**JonathanMoore** · 09-19-2011, 04:52 AM

Thanks for the reply Andrew - i'm just not sure how to add in your suggestion:

It's the "CONCATENATE" function that seems to be causing the issue and whenever I add in the:

Please Login or Register  to view this content.

Code it just places the word "TEXT" before the number that should be the date - have I done this incorrectly?

thanks,

- Jon

**Andrew-R** · 09-19-2011, 05:04 AM

The concatenate function is making a text string, so just replacing the reference to Diesel!$B$2 with the formula I gave, without any quotation marks around it, should include the date/time from $B$2 in the correct format.

**JonathanMoore** · 09-19-2011, 05:11 AM

replacing the cell reference with your formula gives an error (Your Formula Contains an Error) and it highlights the "yyyymmdd" section as the issue

Thanks a lot!

Jon

**Andrew-R** · 09-19-2011, 05:19 AM

Could you post the entire formula you're now trying to use.

It should be:

Please Login or Register  to view this content.

**JonathanMoore** · 09-19-2011, 05:34 AM

I'd missed out the ", before the "TEXT" value... this code works perfectly... thanks

Auto-Created Date field not displaying correctly.

LinkBack

Thread Tools

Rate This Thread

Display

Auto-Created Date field not displaying correctly.

Re: Auto-Created Date field not displaying correctly.

Re: Auto-Created Date field not displaying correctly.

Re: Auto-Created Date field not displaying correctly.

Re: Auto-Created Date field not displaying correctly.

Re: Auto-Created Date field not displaying correctly.

Re: Auto-Created Date field not displaying correctly.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions