Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Format
I have inherited a workbook that has some columns where the person entered
the date as 5/21, 6/10. All the dates are 2007. Is there a way for me to add 2007 to each of the rows so the date appears as "5/21/2007" -- NotGood@All |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Format
Let us first determine if the entries are text or formatted dates.
If the fist date is in A1 what does the formula =ISNUMERIC(A1) return? If TRUE then all you need do is reformat the cells -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "NotGood@All" wrote in message ... I have inherited a workbook that has some columns where the person entered the date as 5/21, 6/10.. All the dates are 2007. Is there a way for me to add "2007" to each of the rows so the date appears as "5/21/2007" -- NotGood@All |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Format
If your data are text strings,
=DATE(2007,LEFT(A1,FIND("/",A1)-1),RIGHT(A1,LEN(A1)-FIND("/",A1))) and format appropriately, or =A1&"/2007" if you just want the result as a text string. -- David Biddulph "NotGood@All" wrote in message ... I have inherited a workbook that has some columns where the person entered the date as 5/21, 6/10.. All the dates are 2007. Is there a way for me to add "2007" to each of the rows so the date appears as "5/21/2007" -- NotGood@All |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Format
I'll bet Bernard meant ISNUMBER
Gord On Tue, 6 Jan 2009 13:12:50 -0400, "Bernard Liengme" wrote: Let us first determine if the entries are text or formatted dates. If the fist date is in A1 what does the formula =ISNUMERIC(A1) return? If TRUE then all you need do is reformat the cells |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Format
Yeep! and every time he type ISNUMERIC (which seems better name to him) he
gets a #NAME? error and he mutters to himself profound philosophical words. Happy New Year, Gord -- Bernard "Gord Dibben" <gorddibbATshawDOTca wrote in message ... I'll bet Bernard meant ISNUMBER Gord On Tue, 6 Jan 2009 13:12:50 -0400, "Bernard Liengme" wrote: Let us first determine if the entries are text or formatted dates. If the fist date is in A1 what does the formula =ISNUMERIC(A1) return? If TRUE then all you need do is reformat the cells |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Format
Been working with VBA where ISNUMERIC is valid.
Happy New Year to you also Bernard. Getting better every day as the snow leaves the ground. Gord On Tue, 6 Jan 2009 17:00:51 -0400, "Bernard Liengme" wrote: Yeep! and every time he type ISNUMERIC (which seems better name to him) he gets a #NAME? error and he mutters to himself profound philosophical words. Happy New Year, Gord |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Format
Thank you all, I did use "isnumber". Some of the fields (45000) are date,
some text, and some general. So my question now is can something be written to first; format each cell to a date format, second, keep the first 5 characters, then add "/2007" -- NotGood@All "David Biddulph" wrote: If your data are text strings, =DATE(2007,LEFT(A1,FIND("/",A1)-1),RIGHT(A1,LEN(A1)-FIND("/",A1))) and format appropriately, or =A1&"/2007" if you just want the result as a text string. -- David Biddulph "NotGood@All" wrote in message ... I have inherited a workbook that has some columns where the person entered the date as 5/21, 6/10.. All the dates are 2007. Is there a way for me to add "2007" to each of the rows so the date appears as "5/21/2007" -- NotGood@All |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Format
If you want to turn a date into text in a specific date format, use the TEXT
function. If you want the first 5 characters, use the LEFT function. If you want to concatenate an extra string, use the CONCATENATE function, or the & operator as shown below. If you want to do things the other way round you can use the DAY and MONTH functions to extract those parts, and use DATE(2007,your_month,your_day) to get a real date in 2007. If you don't know how any of those functions work, they are all standard Excel functions and shown in Excel help. -- David Biddulph NotGood@All wrote: Thank you all, I did use "isnumber". Some of the fields (45000) are date, some text, and some general. So my question now is can something be written to first; format each cell to a date format, second, keep the first 5 characters, then add "/2007" If your data are text strings, =DATE(2007,LEFT(A1,FIND("/",A1)-1),RIGHT(A1,LEN(A1)-FIND("/",A1))) and format appropriately, or =A1&"/2007" if you just want the result as a text string. -- David Biddulph "NotGood@All" wrote in message ... I have inherited a workbook that has some columns where the person entered the date as 5/21, 6/10.. All the dates are 2007. Is there a way for me to add "2007" to each of the rows so the date appears as "5/21/2007" -- NotGood@All |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert serial date format to normal date format | Excel Discussion (Misc queries) | |||
Convert date from text format to date format | Excel Discussion (Misc queries) | |||
Convert date + time text format to date format | Excel Worksheet Functions | |||
Can I change a date with no format (20051111) to date format? | New Users to Excel | |||
Excel 2000 date format cannot be set to Australian date format | Excel Discussion (Misc queries) |