Excel Help - Date issues -only need first part

philwalker531 · 11-02-2009, 01:01 PM

Firstly an apology if this is a numpty question but how do i get excel to display just the first part of the date, i.e 2 or 15 or 31, and not 2-nov-09 etc .

i have a table which repeats monthly and i am using lookup depending on todays date to bring me the appropriate entry/ result.

i have absolutely no idea how to do this - any ideas??

**Glaswegian** · 11-02-2009, 01:24 PM

Hi and welcome.

Use Format > Cells > Custom Format and set it to 'DD'. Bear in mind that Excel will still see the true date - it's only the look of the value that you are changing.

philwalker531 · 11-02-2009, 01:47 PM

Thanks, have just tried it and yes it does show the right format but for some reason it does not find it using the lookup ?

Here is the cell i am using to find the entry from the table depending on waht day it is =VLOOKUP($D$63,$D$67:$E$97,2) the D63 cell is the one i need for the date, excel still sees the info as 40119.86542 ??????????

**Glaswegian** · 11-02-2009, 02:24 PM

Hi

Excel sees dates simply as a number - this allows you to use dates in calculations etc. What is your lookup table based upon? If Excel is using D63 as a date, the lookup column also needs to include dates.

philwalker531 · 11-02-2009, 03:01 PM

my table is a list of 1 through to 31 and the adjacent column has a name attached to each of the 'date' numbers The actual date is irrelevent i just need to ignore the rest of the date and just use the first number from it ie the date 1st 2nd 3rd etc.

**Glaswegian** · 11-02-2009, 03:18 PM

Hi again

There may be a better way to do this but this will work. You'll need to extract the first 2 digits using a formula - which means you'll need to use another cell as the lookup. In a cell beside your 2 digit date, use this formula

=LEFT(TEXT(H4,"DD"),2)

Remember to change the cell reference to suit.

philwalker531 · 11-03-2009, 02:18 AM

I don't know how you know all this stuff but its spot on!!!! thanks for your efforts :)

11-02-2009, 01:01 PM	#1 (permalink)
philwalker531 Registered User Join Date: Nov 2009 Posts: 5 OS: xp	Excel Help - Date issues -only need first part Firstly an apology if this is a numpty question but how do i get excel to display just the first part of the date, i.e 2 or 15 or 31, and not 2-nov-09 etc . i have a table which repeats monthly and i am using lookup depending on todays date to bring me the appropriate entry/ result. i have absolutely no idea how to do this - any ideas?? Last edited by philwalker531; 11-02-2009 at 01:18 PM. Reason: spellign error

11-02-2009, 01:24 PM	#2 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,571 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Excel Help - Date issues -only need first part Hi and welcome. Use Format > Cells > Custom Format and set it to 'DD'. Bear in mind that Excel will still see the true date - it's only the look of the value that you are changing. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

11-02-2009, 01:47 PM	#3 (permalink)
philwalker531 Registered User Join Date: Nov 2009 Posts: 5 OS: xp	Re: Excel Help - Date issues -only need first part Thanks, have just tried it and yes it does show the right format but for some reason it does not find it using the lookup ? Here is the cell i am using to find the entry from the table depending on waht day it is =VLOOKUP($D$63,$D$67:$E$97,2) the D63 cell is the one i need for the date, excel still sees the info as 40119.86542 ??????????

11-02-2009, 02:24 PM	#4 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,571 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Excel Help - Date issues -only need first part Hi Excel sees dates simply as a number - this allows you to use dates in calculations etc. What is your lookup table based upon? If Excel is using D63 as a date, the lookup column also needs to include dates. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

11-02-2009, 03:01 PM	#5 (permalink)
philwalker531 Registered User Join Date: Nov 2009 Posts: 5 OS: xp	Re: Excel Help - Date issues -only need first part my table is a list of 1 through to 31 and the adjacent column has a name attached to each of the 'date' numbers The actual date is irrelevent i just need to ignore the rest of the date and just use the first number from it ie the date 1st 2nd 3rd etc.

11-02-2009, 03:18 PM	#6 (permalink)
Glaswegian Moderator/ Rangemaster TSF Academy; Analyst, Security Team; Oor Wullie; TSF Surgeon and Resident Comic Join Date: Sep 2005 Location: Glasgow Posts: 25,571 OS: Win XP Pro SP3 / Win 7 Pro My System Blog Entries: 10	Re: Excel Help - Date issues -only need first part Hi again There may be a better way to do this but this will work. You'll need to extract the first 2 digits using a formula - which means you'll need to use another cell as the lookup. In a cell beside your 2 digit date, use this formula =LEFT(TEXT(H4,"DD"),2) Remember to change the cell reference to suit. __________________ Iain - Defender of the Haggis and all things Scottish. I don't help by PM - post in the Forums. PC Safety & Security::PC running a bit slow?::Donate::Photographers Corner

11-03-2009, 02:18 AM	#7 (permalink)
philwalker531 Registered User Join Date: Nov 2009 Posts: 5 OS: xp	Re: Excel Help - Date issues -only need first part I don't know how you know all this stuff but its spot on!!!! thanks for your efforts :)