Convert Continuous Time 'Range' into Array of Discrete Time Values?

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

Hi All,

Using worksheet formulae, not VBA, I would like to be able to convert
a time range (for example 22 Jan 2004, 6am through 22 Jan 2004, 9am =
38008.25 to 38008.375) into an array of discrete time values (for
example every 30 mins).

This would mean input cells containing, say:

38008.25
38008.375
((30/60)/24) = 0.0208333333333333

and an output array of say:

{38008.25,38008.2708333333,38008.2916666667,38008.3125,38008.333333333
3,38008.3541666667,38008.375}

I feel that this should be quite easy but I am having a brain block on
it so any help would be appreciated.

Thanks,

Alan.
 
A B
1 22/1/2004 6:00 38008.2500000000
2 22/1/2004 6:30 38008.2708333333
3 22/1/2004 7:00 38008.2916666667
4 22/1/2004 7:30 38008.3125000000
5 22/1/2004 8:00 38008.3333333333
6 22/1/2004 8:30 38008.3541666667
7 22/1/2004 9:00 38008.3750000000

1. Enter Formula =A1+"0:30:00" into Cell A2 and copy it downward.
2. Enter Formula =A1 into Cell B1 with Format of 0.0000000000 and copy it downward.
 
If A1:A7 contains

1/22/04 6:00 AM
1/22/04 6:30 AM
1/22/04 7:00 AM
1/22/04 7:30 AM
1/22/04 8:00 AM
1/22/04 8:30 AM
1/22/04 9:00 AM

Select B1:B7

Put in the formula bar:

=DATEVALUE(TEXT(A1:A7,"m/d/yy h:mm AM/PM"))+TIMEVALUE(TEXT(A1:A7,"m/d/yy
h:mm AM/PM"))

and array-enter [i.e. press Ctrl + Shift + Enter]

Format B1:B7 to desired # of decimal places

B1:B7 will return the output array
 
Scratch earlier suggestion ...

Simpler just to:

Select B1:B7

Put in the formula bar:

=A1:A7+0

(or =A1:A7*1)

and array-enter [i.e. press Ctrl + Shift + Enter]

Format B1:B7 to desired # of decimal places

B1:B7 will return the output array
 
Max said:
Scratch earlier suggestion ...

Simpler just to:

Select B1:B7

Put in the formula bar:

=A1:A7+0

(or =A1:A7*1)

and array-enter [i.e. press Ctrl + Shift + Enter]

Format B1:B7 to desired # of decimal places

B1:B7 will return the output array
Click to expand...

Hi Guys,

Re-reading my OP I was not explicit enough - apologies for that.

I would like to be enter the three inputs into cells:

38008.25
38008.375
((30/60)/24) = 0.0208333333333333

*However*, these particular inputs produce an array answer which is 1
by 7 elements (or 7 by 1).

What if my third input was, say, 20 mins ((20/60)/24) though?

The answer would then be 10 elements so using a fixed range (B1:B7
say) will not work.

I am guessing I need to use the INDIRECT function at the end to derive
the range?

Hope that helps clarify a bit more and I feel I am closer now with
your help!

Alan.
 
Back
Top