Click here to Skip to main content
13,660,425 members
Rate this:
 
Please Sign up or sign in to vote.
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
Updated 10-Nov-17 7:56am
Comments
Richard MacCutchan 11-Nov-17 6:21am
   
Open the spreadsheet and use the Format cells function to set the display of the dates.
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?
Rate this: bad
 
good
Please Sign up or sign in to vote.

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.
  Permalink  
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
cvogt61457 10-Nov-17 20:13pm
   
Or it is a stringed musical instrument.
You are correct about the spelling.
Fixed it.
ppolymorphe 10-Nov-17 21:27pm
   
Technically, it is 'voilà', a French word. :)
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
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

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!
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01-2016 | 2.8.180810.1 | Last Updated 11 Nov 2017
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100