Help

Working hours

Topic Labels: Formulas
Solved
Jump to Solution
2120 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Nahkan
4 - Data Explorer
4 - Data Explorer

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!

Screenshot 2023-02-06 at 11.46.57.png

1 Solution

Accepted Solutions
plyske
7 - App Architect
7 - App Architect

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

1 Reply 1
plyske
7 - App Architect
7 - App Architect

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".