Convert dates to iso 8601 string without code

Question

0.00/5 (No votes)

See more:

1. i need to generate a spreadsheet column of dates in modified iso 8601

2. the format i need is yyyymmdd: no hyphens, no minutes, no seconds

3. the column is for the dates spread jan 1, 1900 to dec 31, 2025

4. i assume there is a simple tool that generates the column. i have not yet found it

5. any steer wld be appreciated

What I have tried:

extensive internet search. i do not code

Posted 10-Nov-17 7:41am

Member 13514637

Updated 10-Nov-17 7:56am

Add a Solution

Comments

Richard MacCutchan 11-Nov-17 6:21am

Open the spreadsheet and use the Format cells function to set the display of the dates.

Member 13514637 11-Nov-17 10:25am

1. even using a spreadsheet that does convert dates into iso 8601 format, there is still the question of auto-generating in the first place the list, in some format or other, of {jan 1, 1900 - dec 31, 2025} dates

2. does anyone know anywhere online that does auto-generate a list of [leap-year-compliant] dates that i can them throw into openoffice4 and convert to iso 8601?

Richard MacCutchan 11-Nov-17 11:24am

That is over 45,000 dates. Exactly what problem are you trying to solve?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

cvogt61457 · Answer 1 · 2017-11-10T07:56:00

Solution 1

Open your spreadsheet.
Type in a few consecutive dates like "1/1/2017". The spreadsheet should recognize that you entered dates.
You can change the format of the dates to display "1 Jan 2017"

Now the fun part.
Format the data into general number.
You can now see how to expand your list easily.

Change the format back to date.

Voila.

Posted 10-Nov-17 7:56am

cvogt61457

Updated 10-Nov-17 14:11pm

v2

Comments

phil.o 10-Nov-17 16:31pm

It's "Voila" :) "Viola" has a completely different meaning, actually; that's the preterit form of the verb "to rape" :s

Patrice T 10-Nov-17 21:27pm

Technically, it is 'voilà', a French word. :)

cvogt61457 10-Nov-17 20:13pm

Or it is a stringed musical instrument.
You are correct about the spelling.
Fixed it.

OriginalGriff 11-Nov-17 5:07am

Moved from "solutions":
thanks:-

1. it doesn't work on mac 10, excel 12.2.0: excel doesn't generate dates in, or convert numbers to, the modified iso 8601 format that i need. if it did, the whole thing wld be a cinch

2. any solutions wld be appreciated

3. there has to be a simple online tool, similar to the data available at textmechanic.com, where one (1) keys in a range of dates from drop-downs, (2) stipulates the required — in this case customised iso 8601 — format, and (3) gets the data

4. if, incredibly, there is no such tool, it wld be well worth making it!

1. and of course the iso 8601 dates need to be correct re leap years

2. the dates formats of open office 4 dates format do include iso 8601, and one can easily clear away the hyphens, but i'm not about to manually type a list of dates of {125 years x 36[5] days}!

3. have just tried to find a list of iso 8601 code numbers that convert into iso 8601 dates, and then at least i can generate a textmechanic string of numbers that i can convert into iso 8601 dates; but of course i can't find anything

4. the absence of realism and practicality in the smug online discussion of iso 8601 is stunning: the practical need here is a simple list of iso 8601 dates generated automatically without any discussion. creating a simple conversion and generation tool wld be a huge service

Member 13514637 · Answer 2 · 2017-11-11T05:18:00

here is the solution using mac 10, excel 12.2.0, openoffice4, and textedit 1.10 and assumes one's chosen spreadsheet is excel:-

1. in openoffice4 spreadsheet:-
(1) dates formatting: choose iso 8601
(2) spreadsheet column: insert start date in iso 8601 format
(3) drag to auto-generate contiguous dates
(4) if one's spreadsheet is openoffice4, one is now done except
(5) openoffice4 find and replace doesn't stretch to removing the hyphens [do this using textedit, plain text, find and replace]
(6) copy the whole column

2. in textedit:-
(1) paste the column
(2) convert to plain text
(3) use find and replace to remove the hyphens
(4) copy the whole column

3. in excel:-
(1) format the destination column as text
(2) paste. end of exercise

that this is such a performance is scandalous!