Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Contributor III

## Calculate duration in minutes

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

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?

Labels (1)
• ### time interval minutes date

1 Solution

Accepted Solutions
MVP & Luminary

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

6 Replies

May be add minutes as format

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Contributor III
Author

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

MVP & Luminary

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

MVP

One common mistake:

MM - month

mm - minute

So, use 'mm' and 'MM' properly

Contributor III
Author
Thank you. I did not know this.
Or I knew and forgot 😉
Contributor III
Author
Perfect - thank you so much!
Community Browser