Time Sheet Formula

[Paul_Carragher]

Hello,

(A) In my time sheet app I have the following expression. Is it possible to deduct 30mins from the computed total for lunch breaks.

IF(ISBLANK([Time Out]), “000:00:00”, [Time Out]-[Time In])

(B) I also have the following expression which works fine for auto complete the Time In, but for my activity column I would like each employees activity to be pre populated by their previous activity and not the previous activity carried out possibly by others.

ANY(SELECT(Timeheet[Time In],[_RowNumber]=[_THISROW].[_RowNumber]-1))

Regards
Paul.

[SKETCHwade]

put this in your formula, with the quotations.
– “000:30:00”

[Paul_Carragher]

Unfortunately this is not returning a result, but it got me thinking.

What is really required for total hours worked is the following in proper language.

If IF(ISBLANK([Time Out]), “000:00:00”, [Time Out]-[Time In]) and duration is < 004:00:00 then [Time Out]-[Time In]
If duration is > 004:00:00 then ([Time Out]-[Time In])-“001:00:00”

Ideas welcomed. Thanks.

[Steve · Accepted solution]

IFS(
  ISBLANK([Time Out]),
    "000:00:00",
  (([Time Out] - [Time In]) < "004:00:00"),
    ([Time Out] - [Time In]),
  TRUE,
    ([Time Out] - [Time In] - "001:00:00")
)

[Paul_Carragher]

Thanks Steve, unfortunately receiving the message

IF function is used incorrectly:three inputs should be provided — (condition, if-result, else-result).

[Steve]

Corrected per @Gil.

[Gil]

Should be IFS instead of IF

[Paul_Carragher]

Much appreciated all, works good. Well spotted @Gil