Populate cells based on date and time range selection

**garyfahy** · 03-10-2014, 06:02 AM

hi there - this is something I have been struggling with for ages. Is it possible to be able to select a range of dates, and a time interval, and then to automatically fill in cells in a column? to be more specific, I want to be able to select, say 1 Jan 2013 to 31 dec 2013 in one box, then specify a time interval, for example "hourly" and then a column is populated with each interval, i.e. 1 jan 2013 00:00, and then the next one is 1 jan 2013 01:00 etc. I have tired the normal automatic fill tool but it never seems to get it right. It works for the first few days then it all goes funny.

thanks in advance for the help
Gary

**Kaper** · 03-10-2014, 07:32 AM

The source of problem is the way Excel stores date/time (floating point double precision value).
Easy way to avoid "funny numbers" down below would be by using formula.
for instance if first date is in C5 in C6 write
=$C$5+WIERSZ(A1)/24
to have hourly interval
and copy down -> worth considering way to do it:

HTML Code:

of course for starting date in A1 use:
=$A$1+WIERSZ(A1)/24

for 10 minutes intervals
=$C$5+WIERSZ(A1)/144

etc.

Once you like the output it would be good idea to copy and paste as values all cells within the range, because every (even so simple) formula slows down excel a tiny bit.
1000 such formulas you will not notice. But if you have several worksheets with tens thousands - it will finally slow down.

**garyfahy** · 03-10-2014, 08:05 AM

Hi Kaper,

thanks for the response. this doesn't seem to work in my Excel though? it just returns "#NAME?". it seems the function WIERSZ isn't recognised. Any ideas?

**Kaper** · 03-10-2014, 08:43 AM

Whoops, it is from national version.
sorry for that

WIERSZ is just ROW, so:

=$C$5+ROW(A1)/24
=$C$5+ROW(A1)/144
etc.

Populate cells based on date and time range selection

LinkBack

Thread Tools

Rate This Thread

Display

Populate cells based on date and time range selection

Re: Populate cells based on date and time range selection

Re: Populate cells based on date and time range selection

Re: Populate cells based on date and time range selection

Thread Information

Users Browsing this Thread

Similar Threads

Auto-populate a range based on active selection with data from another sheet

Need way to self populate multiple cells based on selection from drop down with NUMBERS

Populate cells based on the selection from a drop down box

[SOLVED] Auto populate several cells based on a selection from drop down li

need to populate several cells based on drop down menu selection

Bookmarks

Bookmarks

Posting Permissions