So in my job, I am trying to find the average calendar days between two dates and average business days. For calendar days, I am using:
=[@[DateLastInReview ]]-[@[DateTimeStamp ]]
For Business days, I am using:
=NETWORKDAYS([@[DateTimeStamp ]], [@[DateLastInReview ]], Holiday)-(1-MOD([@[DateLastInReview ]]-[@[DateTimeStamp ]],1))
They should both give me the dates to a decimal place... but my issues is sometimes the business days are higher than the calendar days. Which makes no sense. Because of how the data is being copied from excel, I had to show the time Stamp separately from date Stamp. But, they are in the same cell when using the NETWORKDAYS equation.
+----------------+---------------+-------------------+------------------+--------+--------+
| DateTimeStamp | DateStampTime | DateLastInReview | LastInReviewTime | PreCal | PreBus |
+----------------+---------------+-------------------+------------------+--------+--------+
| 2018-07-11 | 12:00 AM | 7/12/2018 | 12:00 AM | 1.00 | 1.00 |
| 2018-07-09 | 3:07 PM | 7/10/2018 | 9:42 AM | 0.77 | 0.77 |
| 2018-07-02 | 12:34 PM | 7/3/2018 | 7:45 AM | 0.80 | 1.80 |
| 2018-07-02 | 3:34 PM | 7/3/2018 | 8:06 AM | 0.69 | 1.69 |
| 2018-07-02 | 9:59 AM | 7/3/2018 | 8:06 AM | 0.92 | 1.92 |
| 2018-06-29 | 1:54 PM | 7/2/2018 | 9:52 AM | 2.83 | 1.83 |
| 2018-07-09 | 11:46 PM | 7/11/2018 | 9:16 AM | 1.40 | 2.40 |
| 2018-06-29 | 11:57 AM | 6/29/2018 | 1:58 PM | 0.08 | 0.08 |
| 2018-07-05 | 1:29 PM | 7/6/2018 | 9:08 AM | 0.82 | 1.82 |
| 2018-07-05 | 3:49 PM | 7/6/2018 | 10:21 AM | 0.77 | 1.77 |
| 2018-06-27 | 10:31 AM | 6/28/2018 | 9:38 AM | 0.96 | 1.96 |
| 2018-07-06 | 2:46 PM | 7/9/2018 | 8:58 AM | 2.76 | 1.76 |
| 2018-06-28 | 3:32 PM | 7/10/2018 | 7:12 AM | 11.65 | 7.65 |
| 2018-06-29 | 3:04 PM | 7/2/2018 | 11:24 AM | 2.85 | 1.85 |
| 2018-07-11 | 10:28 AM | 7/11/2018 | 1:25 PM | 0.12 | 0.12 |
| 2018-07-10 | 3:30 PM | 7/11/2018 | 2:29 PM | 0.96 | 1.96 |
| 2018-06-26 | 4:09 PM | 7/3/2018 | 12:42 PM | 6.86 | 5.86 |
| 2018-06-28 | 9:18 AM | 6/28/2018 | 10:58 AM | 0.07 | 0.07 |
| 2018-07-09 | 11:39 PM | 7/11/2018 | 9:06 AM | 1.39 | 2.39 |
| 2018-07-06 | 10:40 AM | 7/6/2018 | 3:00 PM | 0.18 | 0.18 |
| 2018-07-02 | 9:33 AM | 7/2/2018 | 2:12 PM | 0.19 | 0.19 |
| 2018-07-10 | 12:00 AM | 7/10/2018 | 4:39 PM | 0.69 | 0.69 |
| 2018-07-03 | 8:20 AM | 7/6/2018 | 9:00 AM | 3.03 | 2.03 |
| 2018-06-27 | 8:52 AM | 6/29/2018 | 9:07 AM | 2.01 | 2.01 |
| 2018-07-09 | 12:50 PM | 7/11/2018 | 8:56 AM | 1.84 | 2.84 |
| 2018-07-05 | 2:56 PM | 7/6/2018 | 12:53 PM | 0.91 | 1.91 |
| 2018-07-10 | 8:43 AM | 7/10/2018 | 9:42 AM | 0.04 | 0.04 |
| 2018-07-10 | 8:43 AM | 7/10/2018 | 9:42 AM | 0.04 | 0.04 |
+----------------+---------------+-------------------+------------------+--------+--------+
