time add

[Jeff]

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 Sjoblom]

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

 

[Jeff]

Peo,
That doesn't work.
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

 

[Peo Sjoblom]

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

 

[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]

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

=IF(formula>End_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