Calculating Hours & Minutes bewteen times in 2 different days

[MauriceWhelan]

Hi.

I have a [Start Time] column whose value will relate to today e.g. 16.00 and a [Finish Time] column whose value will relate to tomorrow e.g. 02.10

Could someone help with an expression to calculate the different in the 2 times in the following format for example 10.10 hours.

I tried using the following suggestion from the community search (HOUR([Finish Time] - [Start Time]) * 60 + MINUTE([Finish Time] - [Start Time])) / 60 but using the examples above I am getting a -14 result.

Thank you.

[Steve]

Try:

((DATETIME([Finish Time]) + 1) - DATETIME([Start Time]))

[MauriceWhelan · Accepted solution]

Steve:

((DATETIME([Finish Time]) + 1) - DATETIME([Start Time]))

Worked Perfectly, thank you @Steve

[MattWest]

This worked for me. However if i put times for the same dayexample 06:00am to 02:00pm it comes up as 32.00hrs.

Could you please advise how to have this corrected.

[Steve]

Please provide a screenshot of the complete expression that doesn’t work.

[MattWest]

Can i send you the app so far and you fix for me please

[Steve]

Nope.

[lstephens]

Steve,

How do I form this same expression but also taking a break time out of that? I have a field that they enter "time" in hh:mm for their break/lunch that I need subtracted from the total amount of time spent on the job.

[MattWest]

[Suvrutt_Gurjar]

You may wish to try

DATETIME([Finish Time])- DATETIME([StartTime])

[MattWest]

Suvrutt_Gurjar:

DATETIME([Finish Time])- DATETIME([StartTime])

This didnt’t help as it only does the single day. I need to be able to do both over 2 days and single day

[Suvrutt_Gurjar]

Can you please elaborate your requirement in more detail, and your column types ,whether TIME or DateTime type?

[Finish Time]- [StartTime] will give duration in HH:MM:SS format between two DateTime type columns.

[MattWest]

This worked however the date reverts back to 1899. Is there a way to have it as the current day

[Suvrutt_Gurjar]

As requested , can you please share the column type details and your requirements more in detail? As mentioned, the expression and discussion thread in this post so far is about calculating duration in hours and minutes.

[MattWest]

I have got it. I have another question for a different part.

How do you select an item from drop down menu and the next to columns are filled in automatically.
Similar to a vlookup on EXCEL

[Suvrutt_Gurjar]

Good to know you got it.

My suggestion will be to open a new thread for your latest question as it is different than duration calculation.

[MattWest]

can you provide link to this thread

[Suvrutt_Gurjar]

You can use the " + " button at the bottom right of your computer ( as shown in image below) and start a new post thread.