Time Calculations

[Aj2666]

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.

[Jeff_Hager]

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))}

[Aj2666]

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.

[Jeff_Hager]

Here you go:

Date Example

[Aj2666]

Thanks a million @Jeff_Hager for your assistance.

[kyleheney]

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.

[ichilddev]

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.

[Darren_Murphy]

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.

[ichilddev]

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.

[Darren_Murphy]

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

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

[ichilddev]

@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?

Have made the references. Something seems not right?

[Darren_Murphy]

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

[ichilddev]

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)

[Darren_Murphy]

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:

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:

Note that is decimal hours, not hours and minutes.

Do you need the result as hours and minutes?

[ichilddev]

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.

Thanks for the tips to multiply by 24 to the 0.29 to get hours.

Btw how do you get HOurs and minutes?

[Darren_Murphy]

@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))
  
  
• Create another math column to extract the number of minutes:
  – Round((Hours-WholeHours)*600/10)
  
  
• Add an if-then-else column to check if the minutes should be zero-padded:
  
  
• Then finally a template column to stitch all the parts together:
  
  

The end result looks like this:

[ichilddev]

Awesome! Let me go through and try out! Thanks!

[ichilddev]

@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?

3. This is the table for attendance which till now, serving not much purpose.

Thanks in advance!

[Darren_Murphy]

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