random date format

[matpj]

hi,
I am having some problem with date formatting in excel.

I've queried some data from a Lotus notes database, and I get dates in the following format:
20/08/2003 09:40
10/02/2003 07:58:00
10/09/2003 15:48:00

when I try to reformat to 'march-98' format, it does the following thing:

20/08/2003 09:40
February-03
September-03

weird.
I'm guessing my Lotus Notes query has something to do with the first date not having an ss part of the time.
the other two though, how do I get excel to read the dats as October?

thanks,
matt

 

[TonyJollans]

Hi matpj,

Excel will use your system International settings to decide how to interpret dates for which there are multiple possible interpretations. You need to make sure that is in sync with what Lotus Notes is putting out.

However, leaving aside the seconds part, your first date can only be 20-August (dd/mm/yyyy) - there isn't an 8th day of the 20th month. You want the other two dates interpreted as mm/dd/yyyy. I think you could have some data problems here and I would go back to Lotus Notes and make sure you are, at least, getting consistent data.

Enjoy,
Tony

 

[xlbo]

I think I know what this is....If I had to hazard a guess, I'd say that a csv file is being used as an interim staging area here. LN will be outputting the date as dd/mm/yyyy
CSVs then try and interpret as mm/dd/yyyy so where it is possible to convert (ie the 2nd and 3rd dates you have provided), they get converted as dates - then, because of your international settings (I'm guessing UK), the dates are converted back to dd/mm/yyyy by excel - the 1st date can't be converted as there are not 20 months and so comes out as text in the original LN format.....would I be close ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[matpj]

xlbo, I think you hit the nail on the head here...

 

[xlbo]

In that case, is there any way you could save the output file as a .wk4 extension rather than a csv ??? .wk4 files can be opened into excel directly and you would get rid of the pesky csv date conversion. If not, I believe the standard workaround is to make the dates output as text (ie with " " around them and convert back to "true" dates when the file is opened in excel......

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[PepeLeMokko]

Mat, you could also proceed as follows with your existing data :
If data is col A, put following formula in cell B1 :
=IF(ISTEXT(A1);DATEVALUE(A1);A1), pull down as needed ( double-clicking on the handle in the right low corner of the selected cell will extend the formula automatically as long as there is something in the cell to it's left) and format column B in the desired date/time format
Remember that in XL text is always aligned left ( which will be the case for your first date and assuming you did not use the Autofit feature or did not align yourself).

If you want to get rid of col A, select all data in col B, move your cursor to the upper part of the selection and drag everything to the left with the right key depressed.
Choose " copy here as values" from the appearing menu, and that's it.
If you do the work once recording a macro, it will be quit esay to use in the future.

 

[xlbo]

Pepe - unfortunately, it isn't as simple as that - because it has been through a csv, the actual serial numbers for the dates have been messed up ie
10/02/2003 07:58:00 in the original was in mm/dd/yyyy format and therefore shows a date in october but when transferred via a csv, it gets converted to a date in february

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244