ExcelBanter - Calculating Due Date

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Calculating Due Date (https://www.excelbanter.com/excel-programming/426186-calculating-due-date.html)

Little Penny[_3_]

Calculating Due Date

I'm trying to create a formula to determine a due date. My due dates
are calculate base on the date in column "A" and the time in column
"B". The due date window is 2am to 1:59am. If and order is received
by 1:59am it is due that same day by 12:30pm. If it is received after
2am it is due the following day by 12:30. The tricky part for me is
the time between 12am and 1:59am. Any help would appreciated.

A few examples

A B C
DATE RECEIVED TIME RECEIVED Would be Due By
3/27/2009 9:52:10 3/28 by 12:30pm
3/27/2009 14:14:17 3/28 by 12:30pm
3/28/2009 0:28:25 3/28 by 12:30pm
3/28/2009 1:19:02 3/28 by 12:30pm
3/28/2009 2:05:35 3/30 by 12:30pm
3/28/2009 2:49:20 3/30 by 12:30pm

Thanks

Little Penny

Barb Reinhardt

Calculating Due Date

Try something like this in C2

=IF(B2<TIME(2,0,0),A2,A2+1)+TIME(12,30,0)

HTH,
Barb Reinhardt

"Little Penny" wrote:

I'm trying to create a formula to determine a due date. My due dates
are calculate base on the date in column "A" and the time in column
"B". The due date window is 2am to 1:59am. If and order is received
by 1:59am it is due that same day by 12:30pm. If it is received after
2am it is due the following day by 12:30. The tricky part for me is
the time between 12am and 1:59am. Any help would appreciated.

A few examples

A B C
DATE RECEIVED TIME RECEIVED Would be Due By
3/27/2009 9:52:10 3/28 by 12:30pm
3/27/2009 14:14:17 3/28 by 12:30pm
3/28/2009 0:28:25 3/28 by 12:30pm
3/28/2009 1:19:02 3/28 by 12:30pm
3/28/2009 2:05:35 3/30 by 12:30pm
3/28/2009 2:49:20 3/30 by 12:30pm

Thanks

Little Penny
T

Little Penny[_3_]

Calculating Due Date

Thanks for your reply.

Column "C" shows the time. How to I get the due date?

On Sat, 28 Mar 2009 16:26:01 -0700, Barb Reinhardt
wrote:

Try something like this in C2

=IF(B2<TIME(2,0,0),A2,A2+1)+TIME(12,30,0)

HTH,
Barb Reinhardt

"Little Penny" wrote:

I'm trying to create a formula to determine a due date. My due dates
are calculate base on the date in column "A" and the time in column
"B". The due date window is 2am to 1:59am. If and order is received
by 1:59am it is due that same day by 12:30pm. If it is received after
2am it is due the following day by 12:30. The tricky part for me is
the time between 12am and 1:59am. Any help would appreciated.

A few examples

A B C
DATE RECEIVED TIME RECEIVED Would be Due By
3/27/2009 9:52:10 3/28 by 12:30pm
3/27/2009 14:14:17 3/28 by 12:30pm
3/28/2009 0:28:25 3/28 by 12:30pm
3/28/2009 1:19:02 3/28 by 12:30pm
3/28/2009 2:05:35 3/30 by 12:30pm
3/28/2009 2:49:20 3/30 by 12:30pm

Thanks

Little Penny
T

Little Penny[_3_]

Calculating Due Date

I formatted column "C" and the Date and Time now appear

These are some of the reults

A B C
3/27/2009 2:09 AM 3/28/09 12:30 PM (Incorrect)
******After 2am due date should be 3/29 at 12:30

3/27/2009 1:39 AM 3/27/09 12:30 PM (Incorrect)
******Before 2am due date shouls be 3/28 at 12:31

Another Example

A B
3/28/2009 1:59am
*****After 2am due date is 3/29 at 12:32

A B
3/28/2009 2:01am
*****After 2am due date is 3/30 at 12:33

On Sat, 28 Mar 2009 19:12:12 -0400, Little Penny
wrote:

I'm trying to create a formula to determine a due date. My due dates
are calculate base on the date in column "A" and the time in column
"B". The due date window is 2am to 1:59am. If and order is received
by 1:59am it is due that same day by 12:30pm. If it is received after
2am it is due the following day by 12:30. The tricky part for me is
the time between 12am and 1:59am. Any help would appreciated.

A few examples

A B C
DATE RECEIVED TIME RECEIVED Would be Due By
3/27/2009 9:52:10 3/28 by 12:30pm
3/27/2009 14:14:17 3/28 by 12:30pm
3/28/2009 0:28:25 3/28 by 12:30pm
3/28/2009 1:19:02 3/28 by 12:30pm
3/28/2009 2:05:35 3/30 by 12:30pm
3/28/2009 2:49:20 3/30 by 12:30pm

Thanks

Little Penny

Little Penny[_3_]

Calculating Due Date

OI got it

Thanks
On Sat, 28 Mar 2009 19:12:12 -0400, Little Penny
wrote:

I'm trying to create a formula to determine a due date. My due dates
are calculate base on the date in column "A" and the time in column
"B". The due date window is 2am to 1:59am. If and order is received
by 1:59am it is due that same day by 12:30pm. If it is received after
2am it is due the following day by 12:30. The tricky part for me is
the time between 12am and 1:59am. Any help would appreciated.

A few examples

A B C
DATE RECEIVED TIME RECEIVED Would be Due By
3/27/2009 9:52:10 3/28 by 12:30pm
3/27/2009 14:14:17 3/28 by 12:30pm
3/28/2009 0:28:25 3/28 by 12:30pm
3/28/2009 1:19:02 3/28 by 12:30pm
3/28/2009 2:05:35 3/30 by 12:30pm
3/28/2009 2:49:20 3/30 by 12:30pm

Thanks

Little Penny

Bob Phillips[_3_]

Calculating Due Date

Try formatting those cells as

mm/dd/yyyy hh:mm

--
__________________________________
HTH

Bob

"Little Penny" wrote in message
...

Thanks for your reply.

Column "C" shows the time. How to I get the due date?

On Sat, 28 Mar 2009 16:26:01 -0700, Barb Reinhardt
wrote:

Try something like this in C2

=IF(B2<TIME(2,0,0),A2,A2+1)+TIME(12,30,0)

HTH,
Barb Reinhardt

"Little Penny" wrote:

I'm trying to create a formula to determine a due date. My due dates
are calculate base on the date in column "A" and the time in column
"B". The due date window is 2am to 1:59am. If and order is received
by 1:59am it is due that same day by 12:30pm. If it is received after
2am it is due the following day by 12:30. The tricky part for me is
the time between 12am and 1:59am. Any help would appreciated.

A few examples

A B C
DATE RECEIVED TIME RECEIVED Would be Due By
3/27/2009 9:52:10 3/28 by 12:30pm
3/27/2009 14:14:17 3/28 by 12:30pm
3/28/2009 0:28:25 3/28 by 12:30pm
3/28/2009 1:19:02 3/28 by 12:30pm
3/28/2009 2:05:35 3/30 by 12:30pm
3/28/2009 2:49:20 3/30 by 12:30pm

Thanks

Little Penny
T

Bob Phillips[_3_]

Calculating Due Date

Why are they incorrect, you said the next day after 2am, not 2 days hence.

--
__________________________________
HTH

Bob

"Little Penny" wrote in message
...

I formatted column "C" and the Date and Time now appear

These are some of the reults

A B C
3/27/2009 2:09 AM 3/28/09 12:30 PM (Incorrect)
******After 2am due date should be 3/29 at 12:30

3/27/2009 1:39 AM 3/27/09 12:30 PM (Incorrect)
******Before 2am due date shouls be 3/28 at 12:31

Another Example

A B
3/28/2009 1:59am
*****After 2am due date is 3/29 at 12:32

A B
3/28/2009 2:01am
*****After 2am due date is 3/30 at 12:33

On Sat, 28 Mar 2009 19:12:12 -0400, Little Penny
wrote:

I'm trying to create a formula to determine a due date. My due dates
are calculate base on the date in column "A" and the time in column
"B". The due date window is 2am to 1:59am. If and order is received
by 1:59am it is due that same day by 12:30pm. If it is received after
2am it is due the following day by 12:30. The tricky part for me is
the time between 12am and 1:59am. Any help would appreciated.

A few examples

A B C
DATE RECEIVED TIME RECEIVED Would be Due By
3/27/2009 9:52:10 3/28 by 12:30pm
3/27/2009 14:14:17 3/28 by 12:30pm
3/28/2009 0:28:25 3/28 by 12:30pm
3/28/2009 1:19:02 3/28 by 12:30pm
3/28/2009 2:05:35 3/30 by 12:30pm
3/28/2009 2:49:20 3/30 by 12:30pm

Thanks

Little Penny

All times are GMT +1. The time now is 12:28 AM.