Solved: Need to calculate end time in hours and minutes

Griff · 12-15-2020 01:33 AM

I need to calculate what the time will be 7.6 hours after the start time, allowing for breaks taken. The following formula rounds my 7.6 hours to 8 hours. How do I get it to work out 7.6 hours or 7 hours 36 minutes after the start time?

0:07:36 +
MIN( SELECT( Timesheets[Start], AND([Worker]= [_THISROW].[Worker], [Work Date] = [_THISROW].[Work Date] ) ) )
+SUM( SELECT( Timesheets[Break], AND([Worker]= [_THISROW].[Worker], [Work Date] = [_THISROW].[Work Date] ) ) )

Suvrutt_Gurjar

Hi @Griff

Thank you , but I believe there was a typo in my suggestion. For adding 7 hours 36 minutes please try

“007:36:00” +
MIN( SELECT( Timesheets[Start], AND([Worker]= [_THISROW].[Worker], [Work Date] = [_THISROW].[Work Date] ) ) )
+SUM( SELECT( Timesheets[Break], AND([Worker]= [_THISROW].[Worker], [Work Date] = [_THISROW].[Work Date] ) ) )

With earlier suggestion “000:07:36”, I believe it adds 7 minutes 36 seconds.

View solution in original post

Suvrutt_Gurjar

Hi @Griff,

I believe if you mention some more details, it will help in suggesting a suitable approach.

In the expression what “0:07:36” stand for? is it the start time? If so from where it is coming and what type of column it is.
The type of [Start] and [Break] columns and finally what is the column type where the expression itself resides.

Griff

Hi Suvrutt, sorry if i was too unclear.

Our employees work a 38 hour week, so after 7.6 hours or 7:36 minutes their pay structure changes. What I need to do is work out is what time their base hours end.

This formula is intended to calculate that end time and assign it to a Time type variable called [End].
[Start] is the time the shift started (Time type field).
[Break] is a duration type field.

So the [End] time should be 7.6 hours plus the sum of any [Break]'s after the [Start] time. My formula assigns the value to a Time field but it seems to round 7.6 hours to 8 hours

Suvrutt_Gurjar

Hi @Griff,

Thank you.

Please try with

“000:07:36” +
MIN( SELECT( Timesheets[Start], AND([Worker]= [_THISROW].[Worker], [Work Date] = [_THISROW].[Work Date] ) ) )
+SUM( SELECT( Timesheets[Break], AND([Worker]= [_THISROW].[Worker], [Work Date] = [_THISROW].[Work Date] ) ) )

Griff

I am always amazed at how quickly you can solve these problems. Thank you for your assistance. That solved my problem.

However I have now tried to insert that formula in the Initial value of [End] encapsulated in a IFS([Type]<>“Ord”… and it doesn’t work. Does this formula create a Time valiable or do I need to do something else to make it work that way.

Suvrutt_Gurjar

Hi @Griff

Thank you , but I believe there was a typo in my suggestion. For adding 7 hours 36 minutes please try

“007:36:00” +
MIN( SELECT( Timesheets[Start], AND([Worker]= [_THISROW].[Worker], [Work Date] = [_THISROW].[Work Date] ) ) )
+SUM( SELECT( Timesheets[Break], AND([Worker]= [_THISROW].[Worker], [Work Date] = [_THISROW].[Work Date] ) ) )

With earlier suggestion “000:07:36”, I believe it adds 7 minutes 36 seconds.

Suvrutt_Gurjar

You may wish to share more details on your latest expression.
Yes, on your query, if one is adding durations ( “007:36:00” and [Break] ) to a time column, [Start], then I believe the result will be a time value.

Griff

Hi Suvrutt, I’ve done more investigation and found how to make it work, kind of, and have given up looking for a solution using this approach. Nevertheless, thanks for your advice, leading me to better understand things.

By way of background, our standard day is 7.6 hours. Each day an employee can enter one or more shift segments including the following fields, Date/Employee/Start/End/Type. Generally, one Shift segment of Type = “Ord” is all they need so their BaseEndTime is 7.6 hours after they start. However, there may be occasions when they need to enter a second shift segment, e.g. Type = “Sick”. In that case they will still be entitled to Base Pay up to 7.6 hours after they started work that day. The idea, therefore, is to work out when 7.6 hours after either their actual start time (possibly a previous shift segment) or the default start time of 7:00 AM

Unfortunately Date, Time and Duration calculations and conversions in Appsheet are so much more complicated than I am used to. In MS Access dates are stored as declimals where the whole number is number of days from 1/1/1900 and the decimal component is decimal part of a day. That makes calculations relatively straightforward and formatting options make the display meaningful and easy to apply.

AppSheet date handling requires a mind-shift for me that I still haven’t grasped. In this case I tried creating a new Virtual Field called BaseEndTime using the following formula. I then used that field value in in the Initial Value of my [End] field. It’s not working correctly for some reason, however I am giving up because the overhead of the repeated Select statement is not worth carrying. I think I am better off trying to work out another way.

“000:07:36” +
If(MIN( SELECT( Timesheets[Start], AND([Worker]= [_THISROW].[Worker], [Work Date] = [_THISROW].[Work Date] ) ) )
=Time(“000:00:00”)
,MIN( SELECT( Timesheets[Start], AND([Worker]= [_THISROW].[Worker], [Work Date] = [_THISROW].[Work Date] ) ) )
,Time(“000:07:00”))
+SUM( SELECT( Timesheets[Break], AND([Worker]= [_THISROW].[Worker], [Work Date] = [_THISROW].[Work Date] ) ) )

Suvrutt_Gurjar

Hi @Griff,

Thank you for a crisp details.

II believe the above expression needs some changes as below for correct operation

You have currently used “000:07:36” which adds 7 minutes 36 seconds, Please use instead “007:36:00” which will add 7 hours 36 minutes. I have mentioned that in my previous post.
For comparing time type column with a constant time , I believe you need not wrap it with TIME() as you have done, Time(“000:00:00”) . So simply please use “00:00:00”
For time TIME("000:07:00) , please use instead "07:00:00"

In AppSheet, I believe duration constant value needs to be preceded with an extra 0 , so duration of 7 hours is “007:00:00” but time of 7 AM is “07:00:00”

So overall, you may wish to test your expression as below with changes highlighted

“007:36:00” +

IF(

MIN( SELECT( Timesheets[Start], AND([Worker]= [_THISROW].[Worker], [Work Date] = [_THISROW].[Work Date] ) ) )
=“00:00:00”
,MIN( SELECT( Timesheets[Start], AND([Worker]= [_THISROW].[Worker], [Work Date] = [_THISROW].[Work Date] ) ) )
, "00:07:00”
+SELECT( Timesheets[Break], AND([Worker]= [_THISROW].[Worker], [Work Date] = [_THISROW].[Work Date] ) )

)

Removed SUM() from last part. I believe, you may not need it if [Break] is duration type.

Suvrutt_Gurjar

Now about decimal math on time, there are a few functions in AppSheet that you may wish to take a look at , just in case you have not

Griff

Thanks. I didn’t know about the leading zero but it certainly makes it easier to understand. I will have a look at the suggested links you provided to see if I can better understand.

As for the suggested solution, I get this error message, but I don’t understand it. If I figure out how to get it working, do you think it adds too much unnecessary overhead by effectively running the same select statement twice? I thought Select statements like this were very resource hungry and should be avoided?

Arithmetic expression ‘(“12/30/1899 12:07:00 AM”+SELECT(Timesheets[Break],AND(([Worker] = [_THISROW-1].[TimeKey].[Worker]), ([Work Date] = [_THISROW-1].[TimeKey].[Work Date]))))’ has inputs of an invalid type ‘Unknown’

Suvrutt_Gurjar

Could you please share the column configuration details and a few values of [Break]?

Also you still seem to be using “00:07:00” instead of “07:00:00” Because “12/30/1899 12:07:00 AM” indicates somewhere 7 minutes are getting added instead of 7 hours.

It may be difficult to diagonise SELECT(Timesheets[Break],AND(([Worker] = [_THISROW-1].[TimeKey].[Worker]), ([Work Date] = [_THISROW-1].[TimeKey].[Work Date])))) with other expression.

You may wish to test it in isolation to ensure it is producing an output type duration.

Griff

Persistence paid off. The following works. However if I remove the SUM() from the Break part of the formula I get an error message. The key to this, as pointed out in your earlier post, is understanding that Duration has an extra leading zero. I am assuming that, since you helped me persevere with this, you don’t necessarily think it adds an onerous overhead.

Thanks again for your help.

“007:36:00” +
If(MIN( SELECT( Timesheets[Start], AND([Worker]= [_THISROW].[Worker], [Work Date] = [_THISROW].[Work Date] ) ) )
=“00:00:00”
,MIN( SELECT( Timesheets[Start], AND([Worker]= [_THISROW].[Worker], [Work Date] = [_THISROW].[Work Date] ) ) )
,“07:00:00”)
+SUM( SELECT( Timesheets[Break], AND([Worker]= [_THISROW].[Worker], [Work Date] = [_THISROW].[Work Date] ) ) )

Arithmetic expression ‘((“07:36:00”+IF((MIN(SELECT(Timesheets[Start],AND(([Worker] = [_THISROW-1].[TimeKey].[Worker]), ([Work Date] = [_THISROW-1].[TimeKey].[Work Date])))) = “12/30/1899 12:00:00 AM”),MIN(SELECT(Timesheets[Start],AND(([Worker] = [_THISROW-1].[TimeKey].[Worker]), ([Work Date] = [_THISROW-1].[TimeKey].[Work Date])))),“12/30/1899 7:00:00 AM”))+SELECT(Timesheets[Break],AND(([Worker] = [_THISROW-1].[TimeKey].[Worker]), ([Work Date] = [_THISROW-1].[TimeKey].[Work Date]))))’ has inputs of an invalid type ‘Unknown’)

Suvrutt_Gurjar

You are welcome. Excellent to know it finally works the way you want.

Please do test well for all possible edge test cases as per your requirement.