software

i am pulling in a report which has a date and time of the entry in
Column A...for ex 12/12/05 11:52 PM.

I have another column (Column B) which pulls in the date from Column
A..so it would be 12/12/05.

Column C has the time...so it would be 11:52 PM.

But now I want to add an hour to the time...so 11:52 PM would become
12:52 AM.
This I can do, but since the day changed, I want to be able to change
the date from 12/12/05 to 12/13/05 in Column B.

So essentially in the original column, if the time is 11:00 PM (since I
will be adding a hour to make it 12:00 AM of next day) or later than I
want to add a day to Column B.

I tried an if statement, so if the time was 11:00 PM or later it would
add another day, but that only works for that day since Excel uses
serial numbers for date/time.

It probly is something simple, but I just cant figure it out.--
kdp145
------------------------------------------------------------------------
kdp145's Profile: www.excelforum.com/member.php...oamp;userid=29594
View this thread: www.excelforum.com/showthread...hreadid=492953One way ... wherever you refer to the date/time in Col A, to be e.g.
=A5 Time(1,0,0). That will add the hour, then when you apply a
function like Day, it will use the adjusted date/time instead of the
original.
Rgds,
ScottO

quot;kdp145quot; gt; wrote
in message
...
|
| i am pulling in a report which has a date and time of the entry in
| Column A...for ex 12/12/05 11:52 PM.
|
| I have another column (Column B) which pulls in the date from
Column
| A..so it would be 12/12/05.
|
| Column C has the time...so it would be 11:52 PM.
|
| But now I want to add an hour to the time...so 11:52 PM would
become
| 12:52 AM.
| This I can do, but since the day changed, I want to be able to
change
| the date from 12/12/05 to 12/13/05 in Column B.
|
| So essentially in the original column, if the time is 11:00 PM
(since I
| will be adding a hour to make it 12:00 AM of next day) or later
than I
| want to add a day to Column B.
|
| I tried an if statement, so if the time was 11:00 PM or later it
would
| add another day, but that only works for that day since Excel uses
| serial numbers for date/time.
|
| It probly is something simple, but I just cant figure it out.
|
|
| --
| kdp145
| -------------------------------------------------------------------
-----
| kdp145's Profile:
www.excelforum.com/member.php...oamp;userid=29594
| View this thread:
www.excelforum.com/showthread...hreadid=492953
|
Here's one suggestion. I don't know how you are separating the date and time,
but one way is this:

With the time in A2, in B2 =INT(A2) and format as a date; in C2 =MOD(A2,1) and
format as a time (or you could just use the formula =A2 in B2 and C2 and
format appropriately).

If cell A3 is to have the hour added, in cell A3, =A2 1/24 and copy the
formulas in B2 and C2 down 1 row.

Or, if you don't want to see the new time in A3, change the formulas in B3 and
C3 to

=INT(A2 1/24) and =MOD(A2 1/24, 1)

On Mon, 12 Dec 2005 23:03:18 -0600, kdp145
gt; wrote:

gt;
gt;i am pulling in a report which has a date and time of the entry in
gt;Column A...for ex 12/12/05 11:52 PM.
gt;
gt;I have another column (Column B) which pulls in the date from Column
gt;A..so it would be 12/12/05.
gt;
gt;Column C has the time...so it would be 11:52 PM.
gt;
gt;But now I want to add an hour to the time...so 11:52 PM would become
gt;12:52 AM.
gt;This I can do, but since the day changed, I want to be able to change
gt;the date from 12/12/05 to 12/13/05 in Column B.
gt;
gt;So essentially in the original column, if the time is 11:00 PM (since I
gt;will be adding a hour to make it 12:00 AM of next day) or later than I
gt;want to add a day to Column B.
gt;
gt;I tried an if statement, so if the time was 11:00 PM or later it would
gt;add another day, but that only works for that day since Excel uses
gt;serial numbers for date/time.
gt;
gt;It probly is something simple, but I just cant figure it out.


ok i did the exact same thing before adding the hour to the original
field thinking that it was going to add a day automatically if i added
the hour...but it doesnt.

i took column A and added a hour and formatted it as time so it changed
from 11:52 PM to 12:52 PM.

Now i took this column and formatted it as date and got 1/1/00 instead
of 12/13/05.--
kdp145
------------------------------------------------------------------------
kdp145's Profile: www.excelforum.com/member.php...oamp;userid=29594
View this thread: www.excelforum.com/showthread...hreadid=492953
update:
ok so i found the problem that was causing the date to change to
1/0/00. i did not tell you guys that i was actually rounding the time
by quarter hour and then adding an hour.

the problem is when i round the time to quarter hour, the date changes
to 1/0/00. so the problem with the dates is because of the rounding and
not becasue of adding an hour.

just to test, i tried adding a hour and that indeed changed the date.--
kdp145
------------------------------------------------------------------------
kdp145's Profile: www.excelforum.com/member.php...oamp;userid=29594
View this thread: www.excelforum.com/showthread...hreadid=492953
update2:

i finally figured out how to solve the problem.

if anybody cares to know, here is how i solved it:
Column A containes original date and time.
Column B has the date (if the original time is between 10:46PM and
11:59PM, then i want to add a day to the original date).
Column C has the time (original time rounding to quarter hour
another hour)

note: you cannot change the date by taking Column C because when you
round the minutes, the date gets changed to 1/0/00, so even if you add
a day it will be 1/1/00.--
kdp145
------------------------------------------------------------------------
kdp145's Profile: www.excelforum.com/member.php...oamp;userid=29594
View this thread: www.excelforum.com/showthread...hreadid=492953You evidently added an hour to a cell that contained only the time, 11:52 PM,
without the date. My instructions were to add an hour to the cell that
contains BOTH the date and the time.

On Tue, 13 Dec 2005 08:23:48 -0600, kdp145
gt; wrote:

gt;
gt;ok i did the exact same thing before adding the hour to the original
gt;field thinking that it was going to add a day automatically if i added
gt;the hour...but it doesnt.
gt;
gt;i took column A and added a hour and formatted it as time so it changed
gt;from 11:52 PM to 12:52 PM.
gt;
gt;Now i took this column and formatted it as date and got 1/1/00 instead
gt;of 12/13/05.

So is this the correct statment of the problem?

A2 contains a date and time.
You want to round that date/time to the nearest 15 minutes, then add 1 hour.
B2 should contain the date only for that rounded and incremented date/time.
C2 should contain the time only for that rounded and incremented date/time.

in B2: =INT(A2) (C2lt;MOD(A2,1))
in C2: =MOD(ROUND(A2*96,0)/96 1/24,1)

Format B2 as a date; format C2 as a time.

or, in B2 put the formula

=ROUND(A2*96,0)/96 1/24

in C2, =B2

and format these cells to show only the date or time. Note that the value in
both cells will be the date time.

In fact I'm curious as to why you want to split the date and time into 2
separate cells in the first place. You can keep the entire value in one cell
and format it to show, e.g. 12/13/2005 10:52 PMOn Tue, 13 Dec 2005 09:09:00 -0600, kdp145
gt; wrote:

gt;
gt;update2:
gt;
gt;i finally figured out how to solve the problem.
gt;
gt;if anybody cares to know, here is how i solved it:
gt;Column A containes original date and time.
gt;Column B has the date (if the original time is between 10:46PM and
gt;11:59PM, then i want to add a day to the original date).
gt;Column C has the time (original time rounding to quarter hour
gt;another hour)
gt;
gt;note: you cannot change the date by taking Column C because when you
gt;round the minutes, the date gets changed to 1/0/00, so even if you add
gt;a day it will be 1/1/00.