Workday diff - including sunday but not friday

OfficeOurs · ‎Sep 01, 2024

Rather long manual formula, but it does the job.

I've got a series of fields that output the day of week in number format, like this:

VALUE(DATETIME_FORMAT(DATETIME_PARSE(Month_billed_for & 1, "MMMM YYYYD"), "d"))

VALUE(DATETIME_FORMAT(DATETIME_PARSE(Month_billed_for & 2, "MMMM YYYYD"), "d"))

Then I sum them the fields that are not fridays or saturdays.

SUM(

IF(AND(COUNTA({a}), {a} != 6, {a} != 5), 1, 0),

IF(AND(COUNTA({b}), {b} != 6, {b} != 5), 1, 0),

IF(AND(COUNTA({c}), {c} != 6, {c} != 5), 1, 0),

IF(AND(COUNTA({d}), {d} != 6, {d} != 5), 1, 0),

IF(AND(COUNTA({e}), {e} != 6, {e} != 5), 1, 0),

IF(AND(COUNTA({g}), {g} != 6, {g} != 5), 1, 0),

IF(AND(COUNTA({h}), {h} != 6, {h} != 5), 1, 0),

IF(AND(COUNTA({i}), {i} != 6, {i} != 5), 1, 0),

IF(AND(COUNTA({j}), {j} != 6, {j} != 5), 1, 0),

IF(AND(COUNTA({k}), {k} != 6, {k} != 5), 1, 0),

IF(AND(COUNTA({l}), {l} != 6, {l} != 5), 1, 0),

IF(AND(COUNTA({m}), {m} != 6, {m} != 5), 1, 0),

IF(AND(COUNTA({n}), {n} != 6, {n} != 5), 1, 0),

IF(AND(COUNTA({o}), {o} != 6, {o} != 5), 1, 0),

IF(AND(COUNTA({p}), {p} != 6, {p} != 5), 1, 0),

IF(AND(COUNTA({q}), {q} != 6, {q} != 5), 1, 0),

IF(AND(COUNTA({r}), {r} != 6, {r} != 5), 1, 0),

IF(AND(COUNTA({s}), {s} != 6, {s} != 5), 1, 0),

IF(AND(COUNTA({t}), {t} != 6, {t} != 5), 1, 0),

IF(AND(COUNTA({u}), {u} != 6, {u} != 5), 1, 0),

IF(AND(COUNTA({v}), {v} != 6, {v} != 5), 1, 0),

IF(AND(COUNTA({w}), {w} != 6, {w} != 5), 1, 0),

IF(AND(COUNTA({x}), {x} != 6, {x} != 5), 1, 0),

IF(AND(COUNTA({y}), {y} != 6, {y} != 5), 1, 0),

IF(AND(COUNTA({z}), {z} != 6, {z} != 5), 1, 0),

IF(AND(COUNTA({aa}), {aa} != 6, {aa} != 5), 1, 0),

IF(AND(COUNTA({ab}), {ab} != 6, {ab} != 5), 1, 0),

IF(AND(COUNTA({ac}), {ac} != 6, {ac} != 5), 1, 0),

IF(AND(COUNTA({ad}), {ad} != 6, {ad} != 5), 1, 0),

IF(AND(COUNTA({ae}), {ae} != 6, {ae} != 5), 1, 0),

IF(AND(COUNTA({af}), {af} != 6, {af} != 5), 1, 0) )