Calculate duration in minutes

[cosmicyes]

Hello friends,

I need to calculate the duration between 2 timestamps in  minutes (even if it lasts for days).

In my Excel I have:

startdate (dd.mm.yyyy)
starttime (hh:mm)
enddate (dd.mm.yyyy)
endtime (hh:mm)

What I have tried so far:

Interval((date(enddate,'YYYYMMDD') & time(endtime,'HHMM'))-(date(startdate,'YYYYMMDD') & time(starttime,'HHMM'))) as Duration,

Example:
Startdate: 01.08.2018
Starttime: 00:05
Enddate: 01.08.2018
Enddate: 06:05

My solution leads to 600.
The right solution would be 360.
But as 360 minutes are 6 hours I feel that I am not far from the right solution...

Any help?

Thank you in advance

[Anil_Babu_Samineni]

May be add minutes as format

Interval((date(enddate,'YYYYMMDD') & time(endtime,'HHMM'))-(date(startdate,'YYYYMMDD') & time(starttime,'HHMM')), 'MM') as Duration,

[cosmicyes]

Thank you - but no, same result.

I did another try:

date((date(enddate,'YYYYMMDD') & time(endtime, 'hhmm'))-(date(startdate,'YYYYMMDD') & time(starttime, 'hhmm')))/100*60 as Duration

In my first example this leads to 360 --> cool.
But:
Another example
startdate 01.08.2018
starttime 00:35
enddate 01.08.2018
endtime 01:24

my second try leads to 53 but 49 would be right.  Mmmm....

[marcus_sommer · Accepted solution]

If your fields are real dates and times there is no need to format them - if not you need to convert them with date#() and time#(). To merge them they are added like date + time because & is for a string-concatenation (even if it's regocnized in some cases as a number). Further you need to apply the right formatting which are set in lower cases for times. In short try:

Interval((enddate + endtime)-(startdate + starttime),'mm')

- Marcus

[tresesco]

One common mistake:

MM - month

mm - minute

So, use 'mm' and 'MM' properly

[cosmicyes]

Thank you. I did not know this.
Or I knew and forgot 😉

[cosmicyes]

Perfect - thank you so much!