Forum Discussion

mkissel1's avatar
mkissel1
Copper Contributor
Jan 07, 2023
Solved

How to associate a word with 0 hours

I am trying to make a work schedule. I am not good with Excel. I am using an excel sheet that allows me to calculate the total hours worked for each person for that week. The only issue I am having i...
excel
Excel on Mac
Formulas and Functions
  • Rodrigo_'s avatar
    Rodrigo_
    Jan 07, 2023

    Hi mkissel1 ,

    use IF and COUNTIF function to translate "OFF" to zero (0)
    =IF(COUNTIF($C4:$M4,"OFF")>=0,0,"insert your formula here")

PeterBartholomew1's avatar
PeterBartholomew1
Silver Contributor
Jan 07, 2023

mkissel1 

Note: This is not really an answer to the OP,  It assumes 365 and familiarity with dynamic arrays.

= LET(
    time,     IF(ISNUMBER(times), times, 0),
    timeFrom, FILTER(time,event="From:"),
    timeTo,   FILTER(time,event="To:"),
    hours,    24*(timeTo - timeFrom),
    totals,   BYCOL(hours, LAMBDA(x,SUM(x))),
    pay,      totals * hourlyRate,
    TOROW(VSTACK(totals, pay), ,TRUE)
  )

The purpose of the solution is to explore options for accepting an alternating pattern of input data and displaying the result as a single array formula.

 

Resources