samedi 3 décembre 2016

how to rewrite/transpose cells upon conditions?

am trying to do some transposed math in google sheet like:

content of cell A1 = 9:00 (formatted as time) 
content of cell B1 = EMPTY cell (formatted as time)
content of cell C1 = text "Holiday" (formatted as time) 
content of cell D1 = 13:45 (formatted as time)

if A1 contains number "9" or "13" write in cell A2 "7:45" (all cells in sheet are formatted as time) and if A1 is EMPTY or contains text "Holiday" leave A2 EMPTY

same rule set for B1 > B2, C1 > C2 and D1 > D2

and then sum cells A2, B2, C2, D2 as "duration of time" in E2 like "=sum(A2:D2)"

example:

     A     B      C        D       E
1  9:00        Holiday   13:45
2  7:45                   7:45   15:30

so far all my effort gives me #ERROR!s and #VALUE!s

Aucun commentaire:

Enregistrer un commentaire