Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Recognise text as date
Chaps,
Auto-generated report output gives text values for month and year in yyyymmformat, e.g. Jan 2007 is 200701 Problem is these are text. How can I get Excel to recognise this as a date? I've tried YEAR(the text) and get something hideous. I've tried LEFT(the text,4) to get the year out as 2007, but this is still seen as text. e.g. if I try YEAR(the 2007 output), I get 1905... I really should know better, but have been trying to get this working for ages. Any thoughts would be greatly appreciated. Cheers. Tom. e.g. if I try |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Recognise text as date
Hi Tom
Perhaps: =TEXT(--A1,"0000-00-\0\1")+0 It does return the date as the first of the month, which i hope is OK for you. Hope this helps! Richard On 14 Mar, 11:23, mr tom <mr-tom at mr-tom.co.uk.(donotspam) wrote: Chaps, Auto-generated report output gives text values for month and year in yyyymmformat, e.g. Jan 2007 is 200701 Problem is these are text. How can I get Excel to recognise this as a date? I've tried YEAR(the text) and get something hideous. I've tried LEFT(the text,4) to get the year out as 2007, but this is still seen as text. e.g. if I try YEAR(the 2007 output), I get 1905... I really should know better, but have been trying to get this working for ages. Any thoughts would be greatly appreciated. Cheers. Tom. e.g. if I try |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Recognise text as date
Thank you. That's ideal.
Tom. "RichardSchollar" wrote: Hi Tom Perhaps: =TEXT(--A1,"0000-00-\0\1")+0 It does return the date as the first of the month, which i hope is OK for you. Hope this helps! Richard On 14 Mar, 11:23, mr tom <mr-tom at mr-tom.co.uk.(donotspam) wrote: Chaps, Auto-generated report output gives text values for month and year in yyyymmformat, e.g. Jan 2007 is 200701 Problem is these are text. How can I get Excel to recognise this as a date? I've tried YEAR(the text) and get something hideous. I've tried LEFT(the text,4) to get the year out as 2007, but this is still seen as text. e.g. if I try YEAR(the 2007 output), I get 1905... I really should know better, but have been trying to get this working for ages. Any thoughts would be greatly appreciated. Cheers. Tom. e.g. if I try |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i get formula to recognise text as = to a number?(excel) | Excel Worksheet Functions | |||
How to get an Excel function to recognise text | Excel Discussion (Misc queries) | |||
why won't excel recognise times | Setting up and Configuration of Excel | |||
After concatenating data, how do I get Excel to recognise date? | Excel Discussion (Misc queries) | |||
XNPV - cant recognise dates | Excel Discussion (Misc queries) |