time add
 

Hi,
In my worksheet, I have 2 cells (mm/dd/yyyy hh:mm format) indicating
start and end day(time), and a 3rd cell indicating the interval (in minutes)
in Column A.
I'm trying to calculate the time using the formula "=$B$1 +
TIME(0,(ROW()-1) *$B$3,0)". The problem I am having is the time is changing
but the date doesn't. Any idea I can fix the problem?


Jeff

The TIME function will not roll over after 60 minutes but you can replace
it with

$B$1+((ROW()-1)/1440)*$B$3

now it will increment and the date will change as well

--

Regards,

Peo Sjoblom


"Jeff" wrote in message
...
Hi,
In my worksheet, I have 2 cells (mm/dd/yyyy hh:mm format) indicating
start and end day(time), and a 3rd cell indicating the interval (in
minutes)
in Column A.
I'm trying to calculate the time using the formula "=$B$1 +
TIME(0,(ROW()-1) *$B$3,0)". The problem I am having is the time is
changing
but the date doesn't. Any idea I can fix the problem?


Jeff

Peo,
That doesn't work.
Jeff
"Peo Sjoblom" wrote:

The TIME function will not roll over after 60 minutes but you can replace
it with

$B$1+((ROW()-1)/1440)*$B$3

now it will increment and the date will change as well

--

Regards,

Peo Sjoblom


"Jeff" wrote in message
...
Hi,
In my worksheet, I have 2 cells (mm/dd/yyyy hh:mm format) indicating
start and end day(time), and a 3rd cell indicating the interval (in
minutes)
in Column A.
I'm trying to calculate the time using the formula "=$B$1 +
TIME(0,(ROW()-1) *$B$3,0)". The problem I am having is the time is
changing
but the date doesn't. Any idea I can fix the problem?


Jeff

Ok, what's in B1 and B3?

because

=TIME(0,(ROW()-1) *$B$3,0)

will return the same as

=((ROW()-1)/1440)*$B$3

except that the latter will increment after 24 hours while the
former will start again



I just tested your formula by putting a date and time in B1 and an integer
in B3
I put your formula in row 1 and copied down, I did the same with mine and
got identical results except that my time rolled over to another day after
24 hours

--

Regards,

Peo Sjoblom

"Jeff" wrote in message
...
Peo,
That doesn't work.
Jeff
"Peo Sjoblom" wrote:

The TIME function will not roll over after 60 minutes but you can
replace
it with

$B$1+((ROW()-1)/1440)*$B$3

now it will increment and the date will change as well

--

Regards,

Peo Sjoblom


"Jeff" wrote in message
...
Hi,
In my worksheet, I have 2 cells (mm/dd/yyyy hh:mm format)
indicating
start and end day(time), and a 3rd cell indicating the interval (in
minutes)
in Column A.
I'm trying to calculate the time using the formula "=$B$1 +
TIME(0,(ROW()-1) *$B$3,0)". The problem I am having is the time is
changing
but the date doesn't. Any idea I can fix the problem?


Jeff

Peo,
It is working just fine, sorry, i was entering my data on the wrong cell.
Also, I have a cell that indicates end time, and the list of time stamps
should fall within start time and end time. One way to do it is to put a if
statement on every cell, but will that slow down the performance
significantly? is that a better way of doing that? Thanks for your help

Jeff

"Peo Sjoblom" wrote:

Ok, what's in B1 and B3?

because

=TIME(0,(ROW()-1) *$B$3,0)

will return the same as

=((ROW()-1)/1440)*$B$3

except that the latter will increment after 24 hours while the
former will start again



I just tested your formula by putting a date and time in B1 and an integer
in B3
I put your formula in row 1 and copied down, I did the same with mine and
got identical results except that my time rolled over to another day after
24 hours

--

Regards,

Peo Sjoblom

"Jeff" wrote in message
...
Peo,
That doesn't work.
Jeff
"Peo Sjoblom" wrote:

The TIME function will not roll over after 60 minutes but you can
replace
it with

$B$1+((ROW()-1)/1440)*$B$3

now it will increment and the date will change as well

--

Regards,

Peo Sjoblom


"Jeff" wrote in message
...
Hi,
In my worksheet, I have 2 cells (mm/dd/yyyy hh:mm format)
indicating
start and end day(time), and a 3rd cell indicating the interval (in
minutes)
in Column A.
I'm trying to calculate the time using the formula "=$B$1 +
TIME(0,(ROW()-1) *$B$3,0)". The problem I am having is the time is
changing
but the date doesn't. Any idea I can fix the problem?


Jeff

Ok, If you want to copy down the formula until it equals or is greaterthen
the end time you could use

=IF(formulaEnd_time,"",formula)
I don't think it will slow it down very much

also you can change the row part so it is not sensitive where you put the
formula

assuming you put ROW() in the first row it can be replaced with ROW(1:1)
and put in any row

--

Regards,

Peo Sjoblom


"Jeff" wrote in message
...
Peo,
It is working just fine, sorry, i was entering my data on the wrong
cell.
Also, I have a cell that indicates end time, and the list of time
stamps
should fall within start time and end time. One way to do it is to put a
if
statement on every cell, but will that slow down the performance
significantly? is that a better way of doing that? Thanks for your help

Jeff

"Peo Sjoblom" wrote:

Ok, what's in B1 and B3?

because

=TIME(0,(ROW()-1) *$B$3,0)

will return the same as

=((ROW()-1)/1440)*$B$3

except that the latter will increment after 24 hours while the
former will start again



I just tested your formula by putting a date and time in B1 and an
integer
in B3
I put your formula in row 1 and copied down, I did the same with mine
and
got identical results except that my time rolled over to another day
after
24 hours

--

Regards,

Peo Sjoblom

"Jeff" wrote in message
...
Peo,
That doesn't work.
Jeff
"Peo Sjoblom" wrote:

The TIME function will not roll over after 60 minutes but you can
replace
it with

$B$1+((ROW()-1)/1440)*$B$3

now it will increment and the date will change as well

--

Regards,

Peo Sjoblom


"Jeff" wrote in message
...
Hi,
In my worksheet, I have 2 cells (mm/dd/yyyy hh:mm format)
indicating
start and end day(time), and a 3rd cell indicating the interval
(in
minutes)
in Column A.
I'm trying to calculate the time using the formula "=$B$1 +
TIME(0,(ROW()-1) *$B$3,0)". The problem I am having is the time
is
changing
but the date doesn't. Any idea I can fix the problem?


Jeff