Forum Discussion
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 is how to make the days off for each person, which is marked "OFF", to associate with 0 hours. When I try to use the word "OFF", I get #VALUE! If I omit the word OFF, then the formula easily sums the total hours worked for each person. Below is an example of what the schedule looks like
7 Replies
- Hi @mkissel
If your problem is just the word "OFF", You can use this formula:
=IF(ISNONTEXT(A2:I2)=TRUE,"insert your formula here"> One possible solution is to enter 0 in the cell and then use custom cell formatting to show it as "OFF". If the value is not 0 (or is text) it will be shown as normal. Custom formatting is 'window dressing' so even though the cell may display "OFF", calculations will see the 0.
Custom cell formatting code: [=0]"OFF";[<>0]General
For example,
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.
- I am not sure where to put that equation...this is the excel file I am using:
https://www.planday.com/blog/free-employee-shift-schedule-excel/- I'm afraid I cannot get the file on your link, it said that it is not available on my country.
It would be great if you could give me a sample of the file you are using thru here or PM.
