Apologies up front for not knowing excel terminology! I am a self-taught
excel user and have created a table with formulas which I now need to fine
tune, but don't know how. (Not even sure exactly what it's called that i've
created)
I am in the medical field and rely on certain blood tests being done at
certain times after a specific timepoint. I have created a table that when
specific data is entered; an automatic calculation occurs which tells you the
next dates and times certain blood tests are required for the duration of the
study. That was the easy part. My problem now is that if that dpecific
timepoint is say 01/01/2008 at 22:30 - I need my table to calculate what the
time would be 3; 6; 9; 12 hours later which i can get it to do; but the date
would also need to change to allow for the time going past midnight. How do I
do that?
--
Your help is appreciated
K

Hi,

If you have a properly formatted date/ time in A1 this adds 3 hours to it

=A1+TIME(3,0,0)

So 1/1/2008 22:30 becomes 2/1/2008 01:30.

Mike

"Learner" wrote:

Apologies up front for not knowing excel terminology! I am a self-taught
excel user and have created a table with formulas which I now need to fine
tune, but don't know how. (Not even sure exactly what it's called that i've
created)
I am in the medical field and rely on certain blood tests being done at
certain times after a specific timepoint. I have created a table that when
specific data is entered; an automatic calculation occurs which tells you the
next dates and times certain blood tests are required for the duration of the
study. That was the easy part. My problem now is that if that dpecific
timepoint is say 01/01/2008 at 22:30 - I need my table to calculate what the
time would be 3; 6; 9; 12 hours later which i can get it to do; but the date
would also need to change to allow for the time going past midnight. How do I
do that?
--
Your help is appreciated
K

Does the DATE and TIME have to be in one cell; as I currently have these in
different columns?
--
Your help is appreciated
K


"Mike H" wrote:

Hi,

If you have a properly formatted date/ time in A1 this adds 3 hours to it

=A1+TIME(3,0,0)

So 1/1/2008 22:30 becomes 2/1/2008 01:30.

Mike

"Learner" wrote:

Apologies up front for not knowing excel terminology! I am a self-taught
excel user and have created a table with formulas which I now need to fine
tune, but don't know how. (Not even sure exactly what it's called that i've
created)
I am in the medical field and rely on certain blood tests being done at
certain times after a specific timepoint. I have created a table that when
specific data is entered; an automatic calculation occurs which tells you the
next dates and times certain blood tests are required for the duration of the
study. That was the easy part. My problem now is that if that dpecific
timepoint is say 01/01/2008 at 22:30 - I need my table to calculate what the
time would be 3; 6; 9; 12 hours later which i can get it to do; but the date
would also need to change to allow for the time going past midnight. How do I
do that?
--
Your help is appreciated
K

Hi,

The proposed solution would only work if they were. From the way you asked
the question I suspected you had then in 2 cells but I can't think of any way
of rolling the date over automatically with that setup.

Mike

"Learner" wrote:

Does the DATE and TIME have to be in one cell; as I currently have these in
different columns?
--
Your help is appreciated
K


"Mike H" wrote:

Hi,

If you have a properly formatted date/ time in A1 this adds 3 hours to it

=A1+TIME(3,0,0)

So 1/1/2008 22:30 becomes 2/1/2008 01:30.

Mike

"Learner" wrote:

Apologies up front for not knowing excel terminology! I am a self-taught
excel user and have created a table with formulas which I now need to fine
tune, but don't know how. (Not even sure exactly what it's called that i've
created)
I am in the medical field and rely on certain blood tests being done at
certain times after a specific timepoint. I have created a table that when
specific data is entered; an automatic calculation occurs which tells you the
next dates and times certain blood tests are required for the duration of the
study. That was the easy part. My problem now is that if that dpecific
timepoint is say 01/01/2008 at 22:30 - I need my table to calculate what the
time would be 3; 6; 9; 12 hours later which i can get it to do; but the date
would also need to change to allow for the time going past midnight. How do I
do that?
--
Your help is appreciated
K

Well, you could do multiple formulas. Assuming Date in A1, and time in B1.
This date and time would be manually entered, obviously, and the formulas in
A2 and B2 are listed below (and copy down as needed).

The time formula would be modified as follows:
A2: =IF(B1+TIME(3,0,0)1,A1+1,A1)
B2: =IF(B1+TIME(3,0,0)1,B1+TIME(3,0,0)-1,B1+TIME(3,0,0))

Hope this helps.
--
John C


"Learner" wrote:

Does the DATE and TIME have to be in one cell; as I currently have these in
different columns?
--
Your help is appreciated
K


"Mike H" wrote:

Hi,

If you have a properly formatted date/ time in A1 this adds 3 hours to it

=A1+TIME(3,0,0)

So 1/1/2008 22:30 becomes 2/1/2008 01:30.

Mike

"Learner" wrote:

Apologies up front for not knowing excel terminology! I am a self-taught
excel user and have created a table with formulas which I now need to fine
tune, but don't know how. (Not even sure exactly what it's called that i've
created)
I am in the medical field and rely on certain blood tests being done at
certain times after a specific timepoint. I have created a table that when
specific data is entered; an automatic calculation occurs which tells you the
next dates and times certain blood tests are required for the duration of the
study. That was the easy part. My problem now is that if that dpecific
timepoint is say 01/01/2008 at 22:30 - I need my table to calculate what the
time would be 3; 6; 9; 12 hours later which i can get it to do; but the date
would also need to change to allow for the time going past midnight. How do I
do that?
--
Your help is appreciated
K

BRILLIANT! I would never have figured that out on my own; even using the help
button!
Thanks to you BOTH!
--
Your help is appreciated
K


"John C" wrote:

Well, you could do multiple formulas. Assuming Date in A1, and time in B1.
This date and time would be manually entered, obviously, and the formulas in
A2 and B2 are listed below (and copy down as needed).

The time formula would be modified as follows:
A2: =IF(B1+TIME(3,0,0)1,A1+1,A1)
B2: =IF(B1+TIME(3,0,0)1,B1+TIME(3,0,0)-1,B1+TIME(3,0,0))

Hope this helps.
--
John C


"Learner" wrote:

Does the DATE and TIME have to be in one cell; as I currently have these in
different columns?
--
Your help is appreciated
K


"Mike H" wrote:

Hi,

If you have a properly formatted date/ time in A1 this adds 3 hours to it

=A1+TIME(3,0,0)

So 1/1/2008 22:30 becomes 2/1/2008 01:30.

Mike

"Learner" wrote:

Apologies up front for not knowing excel terminology! I am a self-taught
excel user and have created a table with formulas which I now need to fine
tune, but don't know how. (Not even sure exactly what it's called that i've
created)
I am in the medical field and rely on certain blood tests being done at
certain times after a specific timepoint. I have created a table that when
specific data is entered; an automatic calculation occurs which tells you the
next dates and times certain blood tests are required for the duration of the
study. That was the easy part. My problem now is that if that dpecific
timepoint is say 01/01/2008 at 22:30 - I need my table to calculate what the
time would be 3; 6; 9; 12 hours later which i can get it to do; but the date
would also need to change to allow for the time going past midnight. How do I
do that?
--
Your help is appreciated
K

Oops - now I have another dilemma....

I now need to calculate 36 hours & 60 hours after a specific timepoint.
Tried to manipulate it using the formula you gave me; but because it's more
than 24 hours - I fell apart!

Please can you help me out again - the cells I have used are as follows:

F4= 01/07/08
F6= 17:45
--
Your help is appreciated
K


"John C" wrote:

Well, you could do multiple formulas. Assuming Date in A1, and time in B1.
This date and time would be manually entered, obviously, and the formulas in
A2 and B2 are listed below (and copy down as needed).

The time formula would be modified as follows:
A2: =IF(B1+TIME(3,0,0)1,A1+1,A1)
B2: =IF(B1+TIME(3,0,0)1,B1+TIME(3,0,0)-1,B1+TIME(3,0,0))

Hope this helps.
--
John C


"Learner" wrote:

Does the DATE and TIME have to be in one cell; as I currently have these in
different columns?
--
Your help is appreciated
K


"Mike H" wrote:

Hi,

If you have a properly formatted date/ time in A1 this adds 3 hours to it

=A1+TIME(3,0,0)

So 1/1/2008 22:30 becomes 2/1/2008 01:30.

Mike

"Learner" wrote:

Apologies up front for not knowing excel terminology! I am a self-taught
excel user and have created a table with formulas which I now need to fine
tune, but don't know how. (Not even sure exactly what it's called that i've
created)
I am in the medical field and rely on certain blood tests being done at
certain times after a specific timepoint. I have created a table that when
specific data is entered; an automatic calculation occurs which tells you the
next dates and times certain blood tests are required for the duration of the
study. That was the easy part. My problem now is that if that dpecific
timepoint is say 01/01/2008 at 22:30 - I need my table to calculate what the
time would be 3; 6; 9; 12 hours later which i can get it to do; but the date
would also need to change to allow for the time going past midnight. How do I
do that?
--
Your help is appreciated
K

Okay, different methodology, and set up for to handle whatever.

Same assumptions as below, with the following additions:
Cell D1, I have set for how many hours you want to the next time frame to
bump.
Then go to Insert--Name--Define. The name for my example is AddTime, so in
the Names in workbook field, I typed Addtime, then in Refers to, I typed the
following formula:
=$D$1/24

The time frame you enter in cell D1 will be in hours, and is ok to leave
formatted as General (or number as you prefer).

The date is in A1, the time is in B1, column A is formatted to date, column
B is formatted to time.
Formula for A2:
=--(TEXT(A1,"dd-mmmm-yyyy")&" "&TEXT(B1,"hh:mm am/pm"))+AddTime
Formula for B2:
=--(TEXT(A1,"dd-mmmm-yyyy")&" "&TEXT(B1,"hh:mm am/pm"))+AddTime

If you were wondering if they were the same, yes, they are the same. The key
is to ensure your columns are formatted appropriately. The formula otherwise
just combines the two fields from the previous row, makes them a full date
frame with day and time, then adds the correct number of hours.

Hope this helps!.

--
John C


"Learner" wrote:

Oops - now I have another dilemma....

I now need to calculate 36 hours & 60 hours after a specific timepoint.
Tried to manipulate it using the formula you gave me; but because it's more
than 24 hours - I fell apart!

Please can you help me out again - the cells I have used are as follows:

F4= 01/07/08
F6= 17:45
--
Your help is appreciated
K


"John C" wrote:

Well, you could do multiple formulas. Assuming Date in A1, and time in B1.
This date and time would be manually entered, obviously, and the formulas in
A2 and B2 are listed below (and copy down as needed).

The time formula would be modified as follows:
A2: =IF(B1+TIME(3,0,0)1,A1+1,A1)
B2: =IF(B1+TIME(3,0,0)1,B1+TIME(3,0,0)-1,B1+TIME(3,0,0))

Hope this helps.
--
John C


"Learner" wrote:

Does the DATE and TIME have to be in one cell; as I currently have these in
different columns?
--
Your help is appreciated
K


"Mike H" wrote:

Hi,

If you have a properly formatted date/ time in A1 this adds 3 hours to it

=A1+TIME(3,0,0)

So 1/1/2008 22:30 becomes 2/1/2008 01:30.

Mike

"Learner" wrote:

Apologies up front for not knowing excel terminology! I am a self-taught
excel user and have created a table with formulas which I now need to fine
tune, but don't know how. (Not even sure exactly what it's called that i've
created)
I am in the medical field and rely on certain blood tests being done at
certain times after a specific timepoint. I have created a table that when
specific data is entered; an automatic calculation occurs which tells you the
next dates and times certain blood tests are required for the duration of the
study. That was the easy part. My problem now is that if that dpecific
timepoint is say 01/01/2008 at 22:30 - I need my table to calculate what the
time would be 3; 6; 9; 12 hours later which i can get it to do; but the date
would also need to change to allow for the time going past midnight. How do I
do that?
--
Your help is appreciated
K

I can get it to work in a new/blank workbook; but I can't get it to work on
my table.

Can I not email you my document to look at for me?
--
Your help is appreciated
K


"John C" wrote:

Okay, different methodology, and set up for to handle whatever.

Same assumptions as below, with the following additions:
Cell D1, I have set for how many hours you want to the next time frame to
bump.
Then go to Insert--Name--Define. The name for my example is AddTime, so in
the Names in workbook field, I typed Addtime, then in Refers to, I typed the
following formula:
=$D$1/24

The time frame you enter in cell D1 will be in hours, and is ok to leave
formatted as General (or number as you prefer).

The date is in A1, the time is in B1, column A is formatted to date, column
B is formatted to time.
Formula for A2:
=--(TEXT(A1,"dd-mmmm-yyyy")&" "&TEXT(B1,"hh:mm am/pm"))+AddTime
Formula for B2:
=--(TEXT(A1,"dd-mmmm-yyyy")&" "&TEXT(B1,"hh:mm am/pm"))+AddTime

If you were wondering if they were the same, yes, they are the same. The key
is to ensure your columns are formatted appropriately. The formula otherwise
just combines the two fields from the previous row, makes them a full date
frame with day and time, then adds the correct number of hours.

Hope this helps!.

--
John C


"Learner" wrote:

Oops - now I have another dilemma....

I now need to calculate 36 hours & 60 hours after a specific timepoint.
Tried to manipulate it using the formula you gave me; but because it's more
than 24 hours - I fell apart!

Please can you help me out again - the cells I have used are as follows:

F4= 01/07/08
F6= 17:45
--
Your help is appreciated
K


"John C" wrote:

Well, you could do multiple formulas. Assuming Date in A1, and time in B1.
This date and time would be manually entered, obviously, and the formulas in
A2 and B2 are listed below (and copy down as needed).

The time formula would be modified as follows:
A2: =IF(B1+TIME(3,0,0)1,A1+1,A1)
B2: =IF(B1+TIME(3,0,0)1,B1+TIME(3,0,0)-1,B1+TIME(3,0,0))

Hope this helps.
--
John C


"Learner" wrote:

Does the DATE and TIME have to be in one cell; as I currently have these in
different columns?
--
Your help is appreciated
K


"Mike H" wrote:

Hi,

If you have a properly formatted date/ time in A1 this adds 3 hours to it

=A1+TIME(3,0,0)

So 1/1/2008 22:30 becomes 2/1/2008 01:30.

Mike

"Learner" wrote:

Apologies up front for not knowing excel terminology! I am a self-taught
excel user and have created a table with formulas which I now need to fine
tune, but don't know how. (Not even sure exactly what it's called that i've
created)
I am in the medical field and rely on certain blood tests being done at
certain times after a specific timepoint. I have created a table that when
specific data is entered; an automatic calculation occurs which tells you the
next dates and times certain blood tests are required for the duration of the
study. That was the easy part. My problem now is that if that dpecific
timepoint is say 01/01/2008 at 22:30 - I need my table to calculate what the
time would be 3; 6; 9; 12 hours later which i can get it to do; but the date
would also need to change to allow for the time going past midnight. How do I
do that?
--
Your help is appreciated
K

Not to worry - I think I've sorted it!

I didn't realise I would have to change this formula (=$D$1/24) if I was
using different cells to what you used! DUH.....

But I wouldn't have been able to do it without your help!
Thanks

--
Your help is appreciated
K


"Learner" wrote:

I can get it to work in a new/blank workbook; but I can't get it to work on
my table.

Can I not email you my document to look at for me?
--
Your help is appreciated
K


"John C" wrote:

Okay, different methodology, and set up for to handle whatever.

Same assumptions as below, with the following additions:
Cell D1, I have set for how many hours you want to the next time frame to
bump.
Then go to Insert--Name--Define. The name for my example is AddTime, so in
the Names in workbook field, I typed Addtime, then in Refers to, I typed the
following formula:
=$D$1/24

The time frame you enter in cell D1 will be in hours, and is ok to leave
formatted as General (or number as you prefer).

The date is in A1, the time is in B1, column A is formatted to date, column
B is formatted to time.
Formula for A2:
=--(TEXT(A1,"dd-mmmm-yyyy")&" "&TEXT(B1,"hh:mm am/pm"))+AddTime
Formula for B2:
=--(TEXT(A1,"dd-mmmm-yyyy")&" "&TEXT(B1,"hh:mm am/pm"))+AddTime

If you were wondering if they were the same, yes, they are the same. The key
is to ensure your columns are formatted appropriately. The formula otherwise
just combines the two fields from the previous row, makes them a full date
frame with day and time, then adds the correct number of hours.

Hope this helps!.

--
John C


"Learner" wrote:

Oops - now I have another dilemma....

I now need to calculate 36 hours & 60 hours after a specific timepoint.
Tried to manipulate it using the formula you gave me; but because it's more
than 24 hours - I fell apart!

Please can you help me out again - the cells I have used are as follows:

F4= 01/07/08
F6= 17:45
--
Your help is appreciated
K


"John C" wrote:

Well, you could do multiple formulas. Assuming Date in A1, and time in B1.
This date and time would be manually entered, obviously, and the formulas in
A2 and B2 are listed below (and copy down as needed).

The time formula would be modified as follows:
A2: =IF(B1+TIME(3,0,0)1,A1+1,A1)
B2: =IF(B1+TIME(3,0,0)1,B1+TIME(3,0,0)-1,B1+TIME(3,0,0))

Hope this helps.
--
John C


"Learner" wrote:

Does the DATE and TIME have to be in one cell; as I currently have these in
different columns?
--
Your help is appreciated
K


"Mike H" wrote:

Hi,

If you have a properly formatted date/ time in A1 this adds 3 hours to it

=A1+TIME(3,0,0)

So 1/1/2008 22:30 becomes 2/1/2008 01:30.

Mike

"Learner" wrote:

Apologies up front for not knowing excel terminology! I am a self-taught
excel user and have created a table with formulas which I now need to fine
tune, but don't know how. (Not even sure exactly what it's called that i've
created)
I am in the medical field and rely on certain blood tests being done at
certain times after a specific timepoint. I have created a table that when
specific data is entered; an automatic calculation occurs which tells you the
next dates and times certain blood tests are required for the duration of the
study. That was the easy part. My problem now is that if that dpecific
timepoint is say 01/01/2008 at 22:30 - I need my table to calculate what the
time would be 3; 6; 9; 12 hours later which i can get it to do; but the date
would also need to change to allow for the time going past midnight. How do I
do that?
--
Your help is appreciated
K