Figuring hours/min from 2 timedate fields

I have a start field and end field that are timedate fields. For a service call i would select the start time when i arrive, when finished i select the end time. I want to populate a 3rd field (InvoiceTime) with the minutes between the 2 timedate fields for invoicing since i invoice by the hour.

Start 11/8/2019 1:20:00 PM   End 11/8/2019 2:20:00 PM  InvoiceTime 60

Is there a way to do this with a formula??

0 11 984
11 REPLIES 11

Yes! When you perform math on two dates (e.g. EndDate - StartDate) you will be given a value of type Duration which is in the format 000:00:00 - hours:minutes:seconds. You can then use functions such as HOUR(), MINUTE() and SECOND() to get those duration components.

This article will give you a little more detail:

Thanks, John, been checking that out today, just not familiar with programming and functions, so it may take a while but this is a great start. Anyone have an example formula to accomplish this?

In the App formula for InvoiceTime, please try this expression:

MINUTE([End] - [Start])

Thanks John! Here is what i get, must be data type or formatting problem?? It is a DateTime field. See below:

The expression is valid but its result type โ€˜Numberโ€™ is not one of the expected types: Time

Yes, your InvoiceTime column needs to be set as a Number datatype since you want to store the number of Minutes.

Yes I had tried that and it gets returned as a decimal.

Iโ€™m not quite following. The MINUTE() function returns a Number datatype.

Iโ€™ve just realized that I havenโ€™t given you the best formula. The function does not return the Total number of minutes like I thought. Only the Minutes portion of the calculated duration. So if the duration was โ€œ001:23:32โ€ - One hour, twenty three minutes and 32 seconds - the MINUTE() function will return only 23.

To get the Total number of minutes there needs to be just a little bit more to the expression. You would want to use this expression in your InvoiceTime columnsโ€™ App Formula:

HOUR([End] - [Start]) * 60 + MINUTE([End] - [Start])

Now this should return a Number value. InvoiceTime should be defined as Number. If you are seeing something different then Iโ€™d ask that you post a screenshot so I can see what you are seeing.

I just realized the field is a datetime field and it already has data in it in that format from when i moved over the spreadsheet from my old app to AppSheet so i get an error, BUT I created a sample field that is a number field and it works great!! Thanks!

I will just figure out how to format it back to time if i want 1 hour to show up as 1:00 instead of 60 or 0:04 instead of 4, etc.

Just to add on, I think the old app had a datediff() function or something like that that allowed it to get the Value that way instead of converting to a number. Anyway thanks again!

There are various ways to represent the duration of time.

I gave you the expression I did because of this statement: "I want to populate a 3rd field (InvoiceTime) with the minutes between the 2 timedate fieldsโ€ฆ"

I took this literally to mean you wanted the duration represented in minutes.


If you want just the straight Duration (the equivalent of DateDiff()), then you can define the column as type โ€œDurationโ€ and use this expression:

[End] - [Start]

This will give you a result like I implied above โ€œ001:23:32โ€ - which is one hour, twenty-three minutes and thirty-two seconds.


You said you Invoice by the hour so if instead it is more useful to have your result in fractional hours you could amend the expression I previously gave you and divide by 60 to put the result into hours like so:

(HOUR([End] - [Start]) * 60 + MINUTE([End] - [Start])) / 60

The duration of โ€œ001:23:32โ€ would then be represented as 1.38 hours - a Decimal data type


It all depends on what you need.

Perfect! I guess this a lesson to be more detailed in my description next time!
Thanks again!

Top Labels in this Space