Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing Time | Excel Discussion (Misc queries) | |||
mulitiplying time | Excel Worksheet Functions | |||
Calculating tvl time in Excel 2000 Rev.9.2720 | Excel Worksheet Functions | |||
Help - Information with time and date | Excel Discussion (Misc queries) | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions |