- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 08, 2016 08:04 AM
I am looking for a formula that can be used to determine the time worked. I have a table that is a “Check In / Out” which includes two fields in of Check in and Check out. Is there a formula that can be set in another field that will give the total time?
Bonus, is there a way to add ALL of the times from that table?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May 08, 2018 02:44 PM
Thanks so much! That pushed me in the right direction
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May 08, 2018 02:49 PM
Sure!
If you want to group by Year, Month, and Day individually, like I said you can do, you’ll have to create a separate field for each - “Year”, “Month”, and “Day” - that contain formulas breaking out:
just the “Year” from the “Date” field,
DATETIME_FORMAT(Date, 'YYYY')
just the “Month” from the “Date” field,
DATETIME_FORMAT(Date, 'MMMM')
and just the “Day” from the “Date” field,
DATETIME_FORMAT(Date, 'D')
You can hide those fields, but then use them to group first on “Year”, then on “Month”, then on “Day”, and have a nice, nested, collapsible view.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Apr 30, 2019 09:49 AM
Thank you this is exactly what I am looking for.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May 21, 2019 10:11 AM
This is awesome! I converted it to populate days and hours instead of hours and minutes, but it’s off by about an hour? Here’s my current formula:
IF(
{Completed Date/Time},
(DATETIME_DIFF({Completed Date/Time}, {Created Time}, 'days') & " Days " &
MOD( DATETIME_DIFF({Completed Date/Time}, {Created Time}, 'hours'), 24) & " Hours "))
But as you can see, the time differences don’t quite add up properly.
Any suggestions?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dec 23, 2020 06:09 PM
Hi Derek,
Anyway I can use this formula and have it only give me total of hours worked on during working hours. If I have a project that started on 01/12/2020 at 10:00 and ended at 02/12/2020 at 11:00 it calculates hours total but is there a formula that only uses work hours to give you a total and takes away the hours not actually worked. So if there is only 7 hours of work in the day can it calculate between those hours?
Thank you for the help
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 15, 2021 10:00 AM
Thanks so much, i just came across this post since i was looking for such a formula, and it worked
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 15, 2021 10:12 AM
Thanks, this is so clean and simple!
and thanks for the explanation, i’m new to formulas and the explanations help me advance my understanding on how they work
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Aug 02, 2021 11:01 AM
Hey Farshad,
Sorry I didn’t notice your post earlier. I am not sure I understand your question. In any case, were you able to find a solution yet?
- « Previous
- Next »