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.

--
Mike

Ref to "Alan" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...

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

--
Rgds
Max
xl 97
----------------------------------
Use xdemechanik <at>yahoo<dot>com for email
-----------------------------------------
"Alan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
>

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

--
Rgds
Max
xl 97
----------------------------------
Use xdemechanik <at>yahoo<dot>com for email
-----------------------------------------

"Max" <(E-Mail Removed)> wrote in message
news:eJx%(E-Mail Removed)...
>
> 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
>
> --
> Rgds
> Max
>

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.

Sorry, Alan. I'm out of ideas on this.

Maybe others would step-in with offerings for you.

--
Rgds
Max
xl 97
----------------------------------
Use xdemechanik <at>yahoo<dot>com for email
-----------------------------------------

Sorry, Alan. I'm out of ideas on this.

Maybe others would step-in with offerings for you.

--
Rgds
Max
xl 97
----------------------------------
Use xdemechanik <at>yahoo<dot>com for email
-----------------------------------------