# Time Calculations **URL:** \ **Category:** Ask for Help\ **Created:** [January 7, 2020, 3:11am UTC](https://community.glideapps.com/t/time-calculations/3391 "2020-01-07T03:11:52Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Aj2666](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/aj2666/32/1822_2.png) [@Aj2666](https://community.glideapps.com/u/Aj2666)\ **Post date:** [January 7, 2020, 3:11am UTC](https://community.glideapps.com/t/time-calculations/3391/1 "2020-01-07T03:11:52Z")
I have a daily log of worked shifts over 12 months period with the following structure: ShiftStart\_DateTime | ShiftEnd\_DateTime | Total hours between a shift 01/05/2020 08:00 am | 01/06/2020 9:00 am | 1 days 1 hrs 0 mins Note: Total hours between a shift Calculated using array formula discussed in this forum ={“Total hours between a shift”;ARRAYFORMULA(IF(ISBLANK(A2:A),“”,INT(C2:C-B2:B)&" days “&TEXT(C2:C-B2:B,“h”” hrs ““m”” mins “”")))}) and trying to produce a report to show total hours made on monthly basis i.e. Month | Total Hours Jan | 20 Feb | 10 … Dec | 30 Totals | 60 and I am not sure how to go about. ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [January 7, 2020, 3:52am UTC](https://community.glideapps.com/t/time-calculations/3391/2 "2020-01-07T03:52:28Z")
Try this formula. It will give you a number formatted in hours. Then you can simply do a sum on the value. `={"Total hours between a shift";ARRAYFORMULA(IF(ISBLANK(A2:A),"",(C2:C-B2:B)*24))}` ---
**Author:** ![Aj2666](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/aj2666/32/1822_2.png) [@Aj2666](https://community.glideapps.com/u/Aj2666)\ **Post date:** [January 7, 2020, 4:07am UTC](https://community.glideapps.com/t/time-calculations/3391/3 "2020-01-07T04:07:42Z")
Thanks @Jeff_Hager . As I have a log over 12 months how do I sum the number of hours worked per month? Showing how many hours worked per month. ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [January 7, 2020, 6:41am UTC](https://community.glideapps.com/t/time-calculations/3391/4 "2020-01-07T06:41:27Z")
Here you go: > **[Date Example](https://docs.google.com/spreadsheets/d/1XDZrZBZ8BI2KQs-X4XUiyhAk7aKIpRkwx5cx5kxdu0Q/edit?usp=sharing)** > > Sheet23 > > Start time,End time,Total hours between a shift,Month Name,Month Number,Month Name,Month Number,sum Total hours between a... ---
**Author:** ![Aj2666](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/aj2666/32/1822_2.png) [@Aj2666](https://community.glideapps.com/u/Aj2666)\ **Post date:** [January 7, 2020, 7:50am UTC](https://community.glideapps.com/t/time-calculations/3391/5 "2020-01-07T07:50:44Z")
Thanks a million @Jeff_Hager for your assistance. ---
**Author:** ![kyleheney](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/kyleheney/32/41173_2.png) [@kyleheney](https://community.glideapps.com/u/kyleheney)\ **Post date:** [March 29, 2020, 6:56pm UTC](https://community.glideapps.com/t/time-calculations/3391/6 "2020-03-29T18:56:51Z")
Any chance time calculations make it into the Glide Editor? Now that date/time can be easily formatted in the editor, if you could calculate the time between two “Time Only” values, you could easily run a number of calculations without any lag in the app. I could see issues with times that go over two dates, but this could be limited to simple calcs of same-day times. ---
**Author:** ![ichilddev](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/ichilddev/32/45179_2.png) [@ichilddev](https://community.glideapps.com/u/ichilddev)\ **Post date:** [September 3, 2022, 7:56am UTC](https://community.glideapps.com/t/time-calculations/3391/7 "2022-09-03T07:56:55Z")
Hi Wondering if this calculation can be done in Glide Table: 1. Use will choose a month/year 2. Glide table to filter the records of attendance by month i.e say September 2022 and do a Rollup? Thanks for your advise in advance. ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [September 3, 2022, 8:02am UTC](https://community.glideapps.com/t/time-calculations/3391/8 "2022-09-03T08:02:37Z")
Yes, it can be done. Quite a few different ways. How will the user select the year/month? What will the interface look like? Will they just select from a datepicker, or something more elaborate? That’s your starting point, so I need to know what that is before I advise on the rest. ---
**Author:** ![ichilddev](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/ichilddev/32/45179_2.png) [@ichilddev](https://community.glideapps.com/u/ichilddev)\ **Post date:** [September 3, 2022, 8:07am UTC](https://community.glideapps.com/t/time-calculations/3391/9 "2022-09-03T08:07:46Z")
I am thinking of 1. Using a Date picker, 2. Extract Month Year from this date 3. Check against the Table of Records that fall into this month and use a IF-Else to filter the Number of Hours (already a value in each row) 4. Rollup the Resulting Column Is the above efficient? Which Method will extract the Month Year using Glidetable? I am using Math Month Year? I am trying not to rely on the Google Sheet thus the learning curve is steeper. ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [September 3, 2022, 8:28am UTC](https://community.glideapps.com/t/time-calculations/3391/10 "2022-09-03T08:28:24Z")
Yes, you’re on the right track 👍 Although, because you want to ultimately do a rollup, you’re better off just creating a multiple relation. The if-then-else approach is better suited when you just want to do a date based filter. Anyway, this is what I would suggest: - Firstly, you need a user specific date/time column to hold the users selected date - Create a Math column using the following formula: `Year(Date) * 100 + Month(Date)` – This converts the selected date into an integer representing the year and month ![Screen Shot 2022-09-03 at 4.30.52 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/8/e/8e45bd8ea6ffcd80e7ef4097e36fea171be109d5.png) - Now, in the table that contains your data records, create the exact same math column, substituting the date column that you want to match on. - Back in the table that contains your user specific column, create a multiple relation column. This should match the converted date (math) column with the same column in your data table. - You can now do your rollups through that multiple relation column. ---
**Author:** ![ichilddev](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/ichilddev/32/45179_2.png) [@ichilddev](https://community.glideapps.com/u/ichilddev)\ **Post date:** [September 3, 2022, 9:07am UTC](https://community.glideapps.com/t/time-calculations/3391/11 "2022-09-03T09:07:56Z")
> [@Darren\_Murphy](#): > > - Now, in the table that contains your data records, create the exact same math column, substituting the date column that you want to match on. > - Back in the table that contains your user specific column, create a multiple relation column. This should match the converted date (math) column with the same column in your data table. Hi hi Can the Date (User Specific) Column and the Roll of Dates be in the same Table? ![a](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/1/d/1df6e19f69b6a6d96287d7e65e0a76ba46f09ff9.jpeg) Have made the references. Something seems not right? ![Ref](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/e/e/eea6155d662c2175886e6aa80a3e497b428fa0d4.jpeg) ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [September 3, 2022, 9:13am UTC](https://community.glideapps.com/t/time-calculations/3391/13 "2022-09-03T09:13:13Z")
> [@ichilddev](#): > > Can the Date (User Specific) Column and the Roll of Dates be in the same Table? They can, but it’s usually cleaner if you keep them separate. From your screen shot, it looks like your user specific date value is being written to different rows. This suggests to me that you’re starting with a list view, then navigating to a details screen and selecting the date from there. That isn’t going to work. You need to set it up so that your user specific value is always written to the first row. Which App/Screen is this? I can have a quick look and suggest the best way forward. ---
**Author:** ![ichilddev](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/ichilddev/32/45179_2.png) [@ichilddev](https://community.glideapps.com/u/ichilddev)\ **Post date:** [September 3, 2022, 9:17am UTC](https://community.glideapps.com/t/time-calculations/3391/14 "2022-09-03T09:17:13Z")
Engage App - Check Out Records Table. Thanks. I have a Attendance Table which does not have a View Tab yet. I am trying out the feasibility before deciding where to put the Date Picker… Probably in the User Table (User Tab) ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [September 3, 2022, 9:49am UTC](https://community.glideapps.com/t/time-calculations/3391/15 "2022-09-03T09:49:38Z")
Actually, what you have there is okay. The problem is that you are summing a duration column, and that’s giving you a result in days, not hours. If you click into that total column, you’ll see the actual summed value: ![Screen Shot 2022-09-03 at 5.43.06 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/f/a/fadcbbefe5649a1ca25fb9f75554a3171342b3e7.png) That’s 0.294 days, which is just over 7 hours (the correct value). To convert that to hours, you just need to multiply it by 24: ![Screen Shot 2022-09-03 at 5.45.03 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/5/2/520356424a9e1f169136461f215f7bb759e3f115.png) Note that is decimal hours, not hours and minutes. Do you need the result as hours and minutes? ---
**Author:** ![ichilddev](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/ichilddev/32/45179_2.png) [@ichilddev](https://community.glideapps.com/u/ichilddev)\ **Post date:** [September 3, 2022, 9:58am UTC](https://community.glideapps.com/t/time-calculations/3391/16 "2022-09-03T09:58:59Z")
Wow! Thanks for your checks and updates. Summing this part is duration? I thought it was the number of hours. Have no idea, that’s why i thought the total hours should be about 6 or 7 hours. ![a](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/9/9/99b9a7d5b4c49751dab762bb8e021bff21cb55cc.jpeg) Thanks for the tips to multiply by 24 to the 0.29 to get hours. Btw how do you get HOurs and minutes? ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [September 3, 2022, 10:21am UTC](https://community.glideapps.com/t/time-calculations/3391/17 "2022-09-03T10:21:02Z")
> [@ichilddev](#): > > Summing this part is duration? I thought it was the number of hours. Have no idea, that’s why i thought the total hours should be about 6 or 7 hours. Yes, it’s a duration formatted as HH:MM:SS. Under the covers, it’s still a number (of days) - and that’s what you used to get when you subtracted one date/time from another. But Glide changed this behaviour a while back. Apparently the change was needed for AirTable support. > [@ichilddev](#): > > Btw how do you get HOurs and minutes? There is probably an easier way to convert decimal hours to hh:mm, but this is what I do: - Create a math column to extract the whole number of hours – `Trunc(Round(Hours,2))` ![Screen Shot 2022-09-03 at 6.16.25 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/8/0/803245d05ce3e50712fdfdab0df39d1d35737f61.png) - Create another math column to extract the number of minutes: – `Round((Hours-WholeHours)*600/10)` ![Screen Shot 2022-09-03 at 6.17.38 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/a/6/a6a25dcd2f1ffc9176e689c1b4e20bc700d3c4ea.png) - Add an if-then-else column to check if the minutes should be zero-padded: ![Screen Shot 2022-09-03 at 6.18.58 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/4/d/4d123d30506f70b62203399a250e08924a8c0972.png) - Then finally a template column to stitch all the parts together: ![Screen Shot 2022-09-03 at 6.19.10 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/c/b/cb38e1cc027e41b6f77a65408540e42d6505fcfa.png) The end result looks like this: ![Screen Shot 2022-09-03 at 6.19.24 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/c/1/c195316d3542f5f079e8c03dd00dc6becdc97a72.png) ---
**Author:** ![ichilddev](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/ichilddev/32/45179_2.png) [@ichilddev](https://community.glideapps.com/u/ichilddev)\ **Post date:** [September 3, 2022, 1:21pm UTC](https://community.glideapps.com/t/time-calculations/3391/18 "2022-09-03T13:21:59Z")
Awesome! Let me go through and try out! Thanks! ---
**Author:** ![ichilddev](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/ichilddev/32/45179_2.png) [@ichilddev](https://community.glideapps.com/u/ichilddev)\ **Post date:** [September 4, 2022, 1:08am UTC](https://community.glideapps.com/t/time-calculations/3391/19 "2022-09-04T01:08:42Z")
> [@Darren\_Murphy](#): > > They can, but it’s usually cleaner if you keep them separate. Hi @Darren_Murphy need your professional opinion: 1. If just need the monthly hour for each employee, will it be good to just use the User table to check on the reports? 2. Or will it be more efficient to use another table for this purpose? ![user](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/8/b/8b4451663a8739e7c34dcee7b8e82e0e618767ef.png) 1. This is the table for attendance which till now, serving not much purpose. ![attendance](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/c/a/ca8ac4fab9a807aa276de2a78b59b5413e83c058.png) Thanks in advance! ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [September 4, 2022, 2:55am UTC](https://community.glideapps.com/t/time-calculations/3391/20 "2022-09-04T02:55:25Z")
> [@ichilddev](#): > > If just need the monthly hour for each employee, will it be good to just use the User table to check on the reports? That’s probably the easiest, yes. ---
**Author:** ![NoCodeAndy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nocodeandy/32/62530_2.png) [@NoCodeAndy](https://community.glideapps.com/u/NoCodeAndy)\ **Post date:** [January 17, 2024, 4:49pm UTC](https://community.glideapps.com/t/time-calculations/3391/21 "2024-01-17T16:49:21Z")