dates
I have a date and time. I need it to show up as a date and it is not. I
have it imported from another program as: 22.08.2008 00:25:00 If I change the .08. to aug, it works. I can use "replace" and it all works. But the months will change. I think it is a format thing. My excel is set to english US. Is that something to do with it? Thanks for any input. |
dates
Assuming your days and months are always 2 characters, and the year is always
4 characters A1: 22.08.2008 00:25:00 =DATE(MID(A1,7,4),MID(A1,4,2),LEFT(A1,2)) Hope this helps. -- John C "Duane" wrote: I have a date and time. I need it to show up as a date and it is not. I have it imported from another program as: 22.08.2008 00:25:00 If I change the .08. to aug, it works. I can use "replace" and it all works. But the months will change. I think it is a format thing. My excel is set to english US. Is that something to do with it? Thanks for any input. |
dates
Hi Duane
In an adjoining column =--SUBSTITUTE(A1,".","/") copy down as far as required. To "fix" the data, copy your new columnPaste SpecialValues Format the column to whatever date format you prefer. You can then delete the original imported column. -- Regards Roger Govier "Duane" wrote in message ... I have a date and time. I need it to show up as a date and it is not. I have it imported from another program as: 22.08.2008 00:25:00 If I change the .08. to aug, it works. I can use "replace" and it all works. But the months will change. I think it is a format thing. My excel is set to english US. Is that something to do with it? Thanks for any input. |
dates
Not a question of Excel settings but of Windows regional settings (through
the Control Panel). Change them to something which expects a date as 22.08.2008 and the when you import 22.08.2008 00:25:00 it will be recognised as date and time, rather than as text. -- David Biddulph "Duane" wrote in message ... I have a date and time. I need it to show up as a date and it is not. I have it imported from another program as: 22.08.2008 00:25:00 If I change the .08. to aug, it works. I can use "replace" and it all works. But the months will change. I think it is a format thing. My excel is set to english US. Is that something to do with it? Thanks for any input. |
All times are GMT +1. The time now is 12:39 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com