Working hours

Nahkan · ‎Feb 06, 2023

Hi there,

I would like to calculate the extra hours I've worked each day by implementing a formula field. I've currently listed records with "Date", "Start" (date, time, timezone), "End" (date, time, timezone).

How do I make sure the formula only lists the extra working hours? Can I implement a formula where weekend days are calculated differently then weekdays?

Thanks for your help!

plyske · ‎Feb 06, 2023

Hi @Nahkan

In the formula field, try this one:

IF(
OR(
WEEKDAY(Start) = 6,
WEEKDAY(Start) = 7
),
DATETIME_DIFF(End, Start, 'hours') - 0,
DATETIME_DIFF(End, Start, 'hours') - 8
)

This formula assumes that the "Start" field is the starting time of work on a given day, and that the "End" field is the ending time of work on that day. The IF function checks if the "Start" field represents a Saturday (6) or Sunday (7) using the WEEKDAY function, and if so, calculates the difference between "End" and "Start" without subtracting 8 hours. If the "Start" field does not represent a weekend day, the formula subtracts 8 hours from the difference between "End" and "Start".

See Solution in Thread

plyske · ‎Feb 06, 2023

Hi @Nahkan

In the formula field, try this one:

IF(
OR(
WEEKDAY(Start) = 6,
WEEKDAY(Start) = 7
),
DATETIME_DIFF(End, Start, 'hours') - 0,
DATETIME_DIFF(End, Start, 'hours') - 8
)

This formula assumes that the "Start" field is the starting time of work on a given day, and that the "End" field is the ending time of work on that day. The IF function checks if the "Start" field represents a Saturday (6) or Sunday (7) using the WEEKDAY function, and if so, calculates the difference between "End" and "Start" without subtracting 8 hours. If the "Start" field does not represent a weekend day, the formula subtracts 8 hours from the difference between "End" and "Start".