Rounding up Date w/ military time to next day if 12:00 or greater

jacksonjeff25

New Member
Joined
Jan 29, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
The goal is to calculate the number of working days (excludes weekends and holidays) between a submit date and due date. The submit date includes the time of the day. Where i need help...I want to round up to the next day for items submitted after 12:00 PM (12:01 - 23:69). Ideally, I would want it to round to the next working day (example: Friday, January 1, 2021, 21:00 would round to Monday, 1/11/2021), but I will settle for next day. My company blocked me uploading an actual example of the sheet, but I did upload a picture that includes the formulas.

Data columns: Column A is the "submit date" that includes the time. Column C is due date.

Columns with calculations:
- Column B is my attempted to round up to the next day if the submit time was in the PM. I used "ROUNDUP" formula, which worked 4 out of the 11 submit dates that should have been round up. (Red outline box indicated correct rounding. Yellow fill indicates date that should have been rounded to next day.
This is the formula where I need help. My round up formula is inadequate. =ROUNDUP(RawData[@[Submitted Date]],1)

- Column D calculates the working days (excluding weekends & using a reference list of holidays to exclude). This calculation seems to work correctly.
=NETWORKDAYS(RawData[@[Clean Submit Date]],RawData[@[Clean Client Meeting Date]],Reference!$G$3:$G$31)
 

Attachments

  • Capture_excel.JPG
    Capture_excel.JPG
    86.4 KB · Views: 6
  • Capture_holiday dates reference.JPG
    Capture_holiday dates reference.JPG
    33.5 KB · Views: 5

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,628
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Date and Time 2021.xlsm
ABCDE
1Due DateWorking Days
208-Jan-21 12:3012-Jan-212
308-Jan-21 09:3012-Jan-213
4
3b
Cell Formulas
RangeFormula
E2:E3E2=NETWORKDAYS(ROUND(A2,0),D2,Holidays)
Named Ranges
NameRefers ToCells
Holidays='3b'!$J$2:$J$8E2:E3
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top