If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Date Format
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Date Format

#11
August 8th 12, 04:24 PM
 ExcelSavior Junior Member First recorded activity by ExcelBanter: Aug 2012 Posts: 4

Quote:
 Originally Posted by joeu2004[_2_] "Ron Rosenfeld" > wrote: > On the other hand, if the 2-digit year should be > interpreted as any other 2-digit year being entered > with the current default settings for Windows of being > interpreted as being 1930-2029, then one could modify > my text import method to use the formula: > =--(RIGHT(A1,3)&" 1, > "&LEFT(A1,FIND("-",A1)-1)+2000-100*(--LEFT(A1,FIND("-",A1)-1)>29)) And what is the benefit of that over either of my previous suggestions, to wit: For text date: =--(MID(A1,1+FIND("-",A1),99) & " 1, " & LEFT(A1,2)) For numeric date: =--(MONTH(A1) & "/1/" & DAY(A1)) Besides being shorter, my suggestions are not limited to the default interpretation of yy<30, which can be altered in the Regional and Language Options control panel. My text formula does make the assumption that the year is always 2 digits, but the month might not always be 3 characters. You make the opposite assumptions (more likely). Eliminating both assumptions, I would write: =--(MID(A1,1+FIND("-",A1),99) & " 1, " & LEFT(A1,FIND("-",A1)-1)) Still simpler and more flexible, IMHO. No matter. My only previous point was: you introduced the assumption of years >=2000. I merely offered a simpler implementation of __your__ assumption.
I am not quite sure why, but replies haven't been able to post. First of all, let me clarify by saying this is in Excel 2003, which i don't think plays much of a role anymore in the solutions you guys are submitting. Second, joeu: I have used the =DATE(2000+DAY(A1),MONTH(A1),1) formula and got what I wanted to work. The problem is now I need the date to show as a date in text format, i.e. 201301 needs to be 201301 when changed to text format, not the weird coding for dates that Excel defaults to. Is there any way to fix this?
#12
August 8th 12, 10:30 PM posted to microsoft.public.excel.worksheet.functions
 joeu2004[_2_] external usenet poster Posts: 637
Date Format

"ExcelSavior" > wrote:
> I have used the =DATE(2000+DAY(A1),MONTH(A1),1) formula
> and got what I wanted to work. The problem is now I need
> the date to show as a date in text format, i.e. 201301
> needs to be 201301 when changed to text format, not the
> weird coding for dates that Excel defaults to. Is there
> any way to fix this?

I don't know what "weird coding" you are referring to. You can format the
cell(s) or column any way you wish. In this case, it appears that you want
the Custom format yyyymm.

Select the cell(s) or column, right-click and click on Format Cells, then
the Number tab, then Custom. Enter yyyymm into the Type field, and click on
OK.

However, the date will remain numeric, not literally text. I suspect that
it is what you meant.

But if you truly want text -- that ISTEXT(B1) should return TRUE -- you can
do the following:

=TEXT(DATE(2000+DAY(A1),MONTH(A1),1),"yyyymm")

#13
August 9th 12, 01:59 AM posted to microsoft.public.excel.worksheet.functions
 Ron Rosenfeld[_2_] external usenet poster Posts: 888
Date Format

On Wed, 8 Aug 2012 00:00:26 -0700, "joeu2004" > wrote:

>And what is the benefit of that over either of my previous suggestions, to
>wit:
>
>For text date:
>=--(MID(A1,1+FIND("-",A1),99) & " 1, " & LEFT(A1,2))
>
>For numeric date:
>=--(MONTH(A1) & "/1/" & DAY(A1))

=IF(ISTEXT(A1),--(MID(A1,1+FIND("-",A1),99) & " 1, " & LEFT(A1,2)),
DATE(DAY(A1),MONTH(A1),1))

I offered another approach, which obviated the need to first determine if the data being processed is text or not.

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post permanent conversion of 1904 date format to 1900 date format Jos Excel Worksheet Functions 3 July 21st 09 06:32 PM convert serial date format to normal date format Flagworld Excel Discussion (Misc queries) 3 September 23rd 08 01:32 PM Convert date + time text format to date format Paul Ho Excel Worksheet Functions 2 May 22nd 07 05:47 PM code to convert date from TEXT format (03-02) to DATE format (200203) Gauthier[_2_] Excel Programming 0 September 22nd 04 03:26 PM Change a date in text format xx.xx.20xx to a recognised date format concatenator Excel Programming 1 November 24th 03 11:33 PM

All times are GMT +1. The time now is 02:41 AM.