2

A week ago I found help here in creating a formula in Google Sheets. Now the requirements have changed. The new requirement is to create the document as an Excel document. I can't take the formula into Excel like this (in the following you can see the formula):

=map( 
  B2:B20, C2:C20, D2:D20, E2:E20, 
  lambda( 
    startDay, startTime, endDay, endTime, 
    if( 
      startDay * startTime * endDay * endTime * (startDay + startTime <= endDay + endTime), 
      let( 
        businessStart, value("7:00"), businessEnd, value("15:00"), 
        pushToNextDay, startDay + (startTime > businessEnd), 
        startDayOfWeek, weekday(pushToNextDay, 2), 
        effectiveStartDay, switch(startDayOfWeek, 6, pushToNextDay + 2, 7, pushToNextDay + 1, pushToNextDay), 
        startOutsideBusinessHours, or(startTime > businessEnd, startTime < businessStart, startDayOfWeek > 5), 
        effectiveStartTime, if(startOutsideBusinessHours, businessStart, startTime), 
        daysBetween, max(0, networkdays(effectiveStartDay, endDay) - 2), 
        hoursBetween, daysBetween * (businessEnd - businessStart), 
        hoursStartDay, businessEnd - effectiveStartTime, 
        hoursEndDay, endTime - businessStart, 
        if( 
          effectiveStartDay = endDay, 
          endTime - effectiveStartTime, 
          hoursStartDay + hoursEndDay + hoursBetween 
        ) 
      ), 
      iferror(1/0) 
    ) 
  ) 
)

This formula was created to calculate the time difference between incoming emails to replies. So the response time has to be determined. The weekends that fall between the arrival and response of the emails have to be subtracted. In addition, the daily working time (7 am - 3 pm) must be taken into account.

the result of the formula can be seen in column F.

enter image description here

I know this forum is more about Google Sheets. Since I already got the Google Sheets formula in this forum, how can I convert it to Excel?

1 Answer 1

0

Tripping on IFERROR

The formula works in Excel with the exception of the IFERROR function:
IFERROR(value, value_if_error)

In Excel, value_if_error is a required field.

In your Excel formula, if you change IFERROR(1/0) to IFERROR(1/0,"") your formula will work.

In Sheets, value_if_error is an optional field. If omitted, value_if_error returns a blank. It is a common convention in Sheets to replace IFERROR(1/0,"") by IFERROR(1/0) because the latter returns a truly blank cell.

Example: COUNTA("")=1
               COUNTA(IFERROR(1/0))=0

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.