change date based on time

[kdp145]

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.

[ScottO]

One 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

"kdp145" <[email protected]> wrote
in message
news:[email protected]...
|
| 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:
http://www.excelforum.com/member.php...o&userid=29594
| View this thread:
http://www.excelforum.com/showthread...hreadid=492953
|

[Myrna Larson]

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
<[email protected]> wrote:

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

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]

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]

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.

[Myrna Larson]

You 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
<[email protected]> wrote:

>
>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.

[Myrna Larson]

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)+(C2<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 PM


On Tue, 13 Dec 2005 09:09:00 -0600, kdp145
<[email protected]> wrote:

>
>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.