Dayfile in Excel?

[bnwrx]

I convert the Cumulus dayfile data to an Excel spreadsheet, but when I do I can not get it to display the date in US format(ie:mm/dd/yy) I have tried when importing the data to change the date column, but it always stays "dd/mm/yy". After importing, I have tried to reformat the cells in Excel, but again it does not change. I am baffled by this. Can anyone help? I know it can be done as evidenced by this that I found on another Cumulus users website:

Thanks for any suggestions....

[daj]

Highligh the cells
Format, Cells
Custom
type mm/dd/yyyy in the box

It certainly worked for me

[bnwrx]

Thanks David, but it does not work for me. I've done it EXACTLY as you described, does not work.
It seems so simple. Why???

[daj]

can you attach your excel file?

[steve]

Could it be that Excel doesn't recognise dd/mm/yy as a date for you, because your system expects mm/dd/yy (so some dates will appear invalid)? Hence it doesn't then let you format it as a date? Just a guess...

[bnwrx]

Here is my Excel sheet:

[artworksmetal]

Crazy. I think I figured it out. Excel is importing the data from dayfile as a US date where it can. So it already thinks that 8/1/2010 is August 1st. When you reformat it, it doesn't change. The reason it does for Daj is that he's in the UK, so his Excel defaults the import to ddmmyy.
What about when the day goes above 12? Look at the data. Excel doesn't know how to make a date out of it, so it makes it a text field. Hence the single quote in the left of the formula bar : '17/01/11 .
You need to make the conversion during the import, or run code that actually interprets the date and rewrites it in US format. If you need help with that,let me know, and I'll work on it.

PS. I almost went blind when I opened that thing

[bnwrx]

Crazy. I think I figured it out. Excel is importing the data from dayfile as a US date where it can. So it already thinks that 8/1/2010 is August 1st. When you reformat it, it doesn't change. The reason it does for Daj is that he's in the UK, so his Excel defaults the import to ddmmyy.
What about when the day goes above 12? Look at the data. Excel doesn't know how to make a date out of it, so it makes it a text field. Hence the single quote in the left of the formula bar : '17/01/11 .
You need to make the conversion during the import, or run code that actually interprets the date and rewrites it in US format. If you need help with that,let me know, and I'll work on it.

PS. I almost went blind when I opened that thing

I like my colors!!!!

[bnwrx]

Crazy. I think I figured it out. Excel is importing the data from dayfile as a US date where it can. So it already thinks that 8/1/2010 is August 1st. When you reformat it, it doesn't change. The reason it does for Daj is that he's in the UK, so his Excel defaults the import to ddmmyy.
What about when the day goes above 12? Look at the data. Excel doesn't know how to make a date out of it, so it makes it a text field. Hence the single quote in the left of the formula bar : '17/01/11 .
You need to make the conversion during the import, or run code that actually interprets the date and rewrites it in US format. If you need help with that,let me know, and I'll work on it.

PS. I almost went blind when I opened that thing

Excel asks about converting the date format during import, which I always pick "mdy", but to no avail. I really want something simple to display Cumulus data, but I have not found it yet. To make the spreadsheets I currently have is quite tedious....

[mcrossley]

Excel asks about converting the date format during import, which I always pick "mdy", but to no avail.

But isn't the data in dmy format?

[bnwrx]

Excel asks about converting the date format during import, which I always pick "mdy", but to no avail.

But isn't the data in dmy format?

Here is where you can make a choice:

Text Import Wizard.png

But it NEVER converts it...

[mcrossley]

I'm assuming you do change the data type to "Date", and then the dropdown to DMY ?

[artworksmetal]

I think you need to pick DMY, then excel will know which number represents the day. Then after the import you can change the formatting.
I don't have access to my dayfile to experiment right now.

[bnwrx]

I think you need to pick DMY, then excel will know which number represents the day. Then after the import you can change the formatting.
I don't have access to my dayfile to experiment right now.

Exactly.!!

So it must be Excel asks what date format was used originally, in this case d/m/y, then it knows to convert to m/d/y for us here in the States. Wow I would have never thought of that, but now it works.
Very happy and many thanks for everyones help with this!!

[artworksmetal]

Glad to be of some use. Now about those colors....