Is there any function in excel to find day time between two date and time in Excel?-CodePudding

I need a formula to calculate between two date and time excluding lunch time, holidays, weekend and between 09:00 and 18:00 work hours.

For example, 25/07/2022 12:00 and 29/07/2022 10:00 and answer has to be 1 day, 06:00

Thanks in advance.

I had a formula but it didn't work when hours bigger than 24 hours.

CodePudding user response：

I don't know how you got to 1 day and 6 hours, but here is a customizable way to filter your time difference calculation:

=LET(
    start,E3,
    end,E4,
    holidays,$B$3:$B$5,
    array,SEQUENCE(INT(end)-INT(start) 1,24,INT(start),TIME(1,0,0)),
    crit_1,array>=start,
    crit_2,array<=end,
    crit_3,WEEKDAY(array,2)<6,
    crit_4,HOUR(array)>=9,
    crit_5,HOUR(array)<=18,
    crit_6,HOUR(array)<>13,
    crit_7,ISERROR(MATCH(DATE(YEAR(array),MONTH(array),DAY(array)),holidays,0)),
    result,SUM(crit_1*crit_2*crit_3*crit_4*crit_5*crit_6*crit_7),
    result
)

Limitation

This solution only works on an hourly level, i.e. the start and end dates and times will only be considered on a full hour basis. When providing times like 12:45 as input, the 15 minute increment won't be accounted for.

Explanation

The 4th item in the LET() function SEQUENCE(INT(end)-INT(start) 1,24,INT(start),TIME(1,0,0)) creates an array that contains all hours within the start and end date of the range:

(transposed for illustrative purposes)

then, based on that array, the different 'crit_n' statements are the individual criteria you mentioned. For example, crit_1,array>=start means that only the dates and times after the start date and time will be counted, or crit_6,HOUR(array)<>13 is the lunch break (assuming the 13th hour is lunch time), ...

All of the individual crit_n's are then arrays of the same size containing TRUE and FALSE elements.

At the end of the LET() function, by multiplying all the individual crit_n arrays, the product returns a single array that will then only contain those hours where all individual criteria statements are TRUE:

So then the SUM() function is simply returning the total number of hours that fit all criteria.

Example

I assumed lunch hour to be hour 13, and I assumed the 28th to be a holiday within the given range. With those assumptions and the other criteria you already specified above, I'm getting the following result:

Which looks like this when going into the formula bar:

CodePudding user response：

In cell G2, you can put the following formula:

=LET(from,A2:A4,to,B2:B4,holidays,C2:C2,startHr,E1,endHr,E2, lunchS, E3, lunchE, E4, 
  CALC, LAMBDA(date,isFrom, LET(noWkDay, NETWORKDAYS(date,date,holidays)=0,
    IF(noWkDay, 0, LET(d, INT(date), start, d   startHr, end, d   endHr,
      noOverlap, IF(isFrom, date > end, date < start), lunchDur, lunchE-lunchS,
      ls, d   lunchS, le, d   lunchE,
      isInner, IF(isFrom, date > start, date < end),
      diff, IF(isFrom, end-date-1 - IF(date < ls, lunchDur, 0), 
        date-start-1 - IF(date > le, lunchDur, 0)),
      IF(noOverlap, -1, IF(isInner, diff, 0)))))),
  MAP(from,to,LAMBDA(ff,tt, LET(wkdays, NETWORKDAYS(ff,tt,holidays),
    duration, wkdays   CALC(ff, TRUE)   CALC(tt, FALSE),
    days, INT(duration), time, duration - TRUNC(duration),
    TEXT(days, "d") &" days "& TEXT(time, "hh:mm") &" hrs "
  )))
)

and here is the output:

Explanation

Used LET function for easy reading and composition. The main idea is first to calculate the number of working days excluding holidays from column value to to column value. We use for that NETWORKDAYS function. Once we have this value for each row, we need to adjust it considering the first day and last day of the interval, in case we cannot count as a full day and instead considering hours. For inner days (not start/end of the interval) it is counted as an entire day.

We use MAP function to do the calculation over all values of from and to names. For each corresponding value (ff, tt) we calculate the working days (wkdays). Once we have this value, we use the user LAMBDA function CALC to adjust it. The function has a second input argument isFrom to consider both scenarios, i.e. adjustment at the beginning of the interval (isFrom = TRUE) or to the end of the interval (isFrom=FALSE). The first input argument is the given date.

In case the input date of CALC is a non working day, we don't need to make any adjustment. We check it with the name noWkDay. If that is not the case, then we need we need to determine if there is no overlap (noOverlap):

IF(isFrom, date > end, date < start)

where start, end names correspond to the same date as date, but with different hours corresponding to start Hr and end Hr (E1:E2). For example for the first row, there is no overlap, because the end date doesn't have hour information, i.e. (12:00 AM), in such case the corresponding date should not be taken into account and CALC returns -1, i.e. one day needs to be subtracted.

In case we have overlap, then we need to consider the case the working hours are lower than the maximum working hours (from 9:00 to 18:00). It is identified with the name isInner. If that is the case, we calculate the actual hours. We need to subtract 1 because it is going to be one less full working day and instead to consider the corresponding hours (that should be less than 9hrs, which is the maximum workday duration). The calculation is carried under the name diff:

IF(isFrom, end-date-1 - IF(date < ls, lunchDur, 0), 
   date-start-1 - IF(date > le, lunchDur, 0))

If the actual start is before the start of the lunch time (ls), then we need to subtract lunch duration (lunchDur). Similarly if the actual end is is after lunch time, we need to discount it too.

Finally, we use CALC to calculate the interval duration:

wkdays   CALC(ff, TRUE)   CALC(tt, FALSE)

Once we have this information, it is just to put in the specified format indicating days and hours.

Now let's review some of the sample input data and results:

The interval starts on Monday 7/25 and ends on Friday 7/29, therefore we have 5 working days, but 7/26 is a holiday, so the maximum number of working days will be 4 days.
For the interval [7/25, 7/29] starts and ends on midnight (12:00 AM), therefore the last day of the interval should not be considered, so actual working days will be 3.
Interval [7/25 10:00, 7/29 17:00]. For the start of the interval we cannot count one day, instead 8hrs and for the end of the interval, the same situation 8hrs, so instead of 4days we are goin to have 2days plus 16hrs, but we need to subtract in both cases the lunch duration (1hr) so the final result will be 2 days 14hrs.