| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calculating date and placing the value in the right date range col | Excel Discussion (Misc queries) | |||
| Calculating a date | Excel Worksheet Functions | |||
| Calculating Difference Between Start Date & Time And End Date & Ti | Excel Discussion (Misc queries) | |||
| Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions | |||
| Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions | |||
![Little Penny[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
