+1Hi 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!
Best answer by plyske
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".
+11Hi @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".
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
