The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
The Guiding Principles and New Rules document is now in effect.
OMFFFFFG Excel date conversion
GenlyAi
Registered User regular
Registered User regular
Ok, I've been annoyed by this for like 15 years, so I guess this isn't urgent, but at some point my head is going to fucking explode and I prefer to avoid that.
Say I have a CSV or a tab-delimited file that I want to import into motherfucking excel ('03). Say it's got some fields like:
1-3 or 8/12
Excel will convert this shit to its internal date format representing January 3 or August 12. Is there any, ANY way to prevent this? I know I can modify the text file and put some quotes in, but that's bullshit, and it's not always practical. I want to stop this from happening at all.
Excel, why is this your default behavior, motherfucker, when you have a function anyone can use to do this conversion themselves? And whyyyyyyyyyy don't you have an option to turn it off?
Am I being dumb? Please tell me I'm being dumb and there's a solution to this. All I want is for Excel to never convert a date again without me asking it to. I don't care if I have to root my computer to get it, tell me what to do HA.
Say I have a CSV or a tab-delimited file that I want to import into motherfucking excel ('03). Say it's got some fields like:
1-3 or 8/12
Excel will convert this shit to its internal date format representing January 3 or August 12. Is there any, ANY way to prevent this? I know I can modify the text file and put some quotes in, but that's bullshit, and it's not always practical. I want to stop this from happening at all.
Excel, why is this your default behavior, motherfucker, when you have a function anyone can use to do this conversion themselves? And whyyyyyyyyyy don't you have an option to turn it off?
Am I being dumb? Please tell me I'm being dumb and there's a solution to this. All I want is for Excel to never convert a date again without me asking it to. I don't care if I have to root my computer to get it, tell me what to do HA.
GenlyAi on
+1
This discussion has been closed.
Posts
If you're talking about copy/pasting etc., just define the column as "Text" before you paste and select "Match destination formatting"
not that i'm aware of no.
when opening csv's or txt files excel just goes loose.
With CSV/TXT, you should be able to specify column types and break types upon opening it the first time. Just make sure you hit next until you can change them from "General" to "Text"
This is great, but I would still love some way it would work when I just drag a file from windows explorer. If you do that, Excel just makes every column "general".
Going through the Open menu item is a perfectly fine work around
...
it just doesn't scratch my urge to make Excel obey me, to teach it that its normal way is the stupid way and that it is just software while I am a MAN.
But what are you gonna do.
Just remember that excel does what excel pleases, which is a gigantic pain in the ass sometimes. Drag and drop things like that are hardcoded to go all-general, which can be super-frustrating. Endlessly useful program, endlessly aggravating for stupid reasons.
When you paste data into Excel, there should be a small clipboard icon next to the pasted content. Click on it, and you'll get a menu that has the option to define the import format. Select that option, then go through the wizard that pops up to define the column limits and formats.
Yeah, he was looking for doing it to the whole file without dealing with the dialog.
Also, you can re-define the cells or column once imported to show the date in a preferred format. You can even make your own format if you spend a little time learning Excel's context.
Excel, you suck.
1) Excel always stores any date-like figure in its own special data format. This allows it to do things like add/delete days from one another, and maintain the actual date value while being able to display different date formats. It's annoying when you don't want it to do that, but it's a godsend when you do. If you can, I'd recommend keeping two columns to be safe - one with the date stored as-is, and the other with the date stored in Excel's format.
2) Never just "paste" stuff into Excel. You generally want to follow a two-step process:
3) The vast majority of casual use cases I've seen are easily managed by setting the source and destination Formats to "Text" and then Paste Special -> Values. This will allow you to put in something like "12 Nov 2014" or "00141232", copy it as text, and then paste it back in as-is without Excel performing any automatic transformations.