0

Let's say I have two date/times. The start date/time is 29th May 2020 at 20:30 and the end date/time is 30th May at 02:30. This link shows how to calculate using two cell references.

How to calculate in one cell the duration?

Something like DATEDIF but accepting the time and output of result in hours, e.g.:

=MYCLEVERDATETIMEDIFF(“29/5/20 20:30”, “30/5/20 02:30”, "H")

which results in:

6

Six being the elapsed time in hours between the above date/times.

What function, or combination of functions and conversions would do the trick? Note that ideally something that works as one cell function, not calculating the difference between two cell references.

Any ideas?

2

Assuming I have understood what you wish to accomplish correctly, here is one way of doing it in the same cell if you are willing to enter the dates and the times separately:

enter image description here

And here is the text version of the formula shown above

=(DATEDIF(DATE(2020,5,29),DATE(2020,5,30),"D")+TIMEVALUE("02:30")−TIMEVALUE("20:30"))×24

Unfortunately, this can not be done by entering the date & time together for each point in time (or without entering them more than once). It appears that there is no date & time function in Numbers that calculates the difference between two date & time values in hours directly or one that fully converts a date & time value in text or otherwise to a proper date & time value that Numbers can process.

| improve this answer | |
  • Nice, thanks! Weird but this also works!... =24−((TIMEVALUE("29/5/20 20:30")−TIMEVALUE("30/5/20 2:30"))×24) An issue with both however is they return numbers rather than duration type. In either case one can then add the DURATION function as follows to complete: =DURATION(,,(24−((TIMEVALUE("29/5/20 20:30")−TIMEVALUE("30/5/20 2:30"))×24))) – Max MacLeod May 31 at 20:12
  • Happy to hear that you figured out other formulas that may work for you. Pls note that the formulas you suggest right above would calculate the difference in hours correctly only if the dates are consecutive. – Alper Jun 1 at 10:45
1

Just click a cell and type the equals sign, then add DateDif(ref cell 1, reference cell 2,"D"). You can enter reference cell 1 and 2 by just clicking on the proper cell. This command will return the number of days between the two dates. It is done in one cell.

| improve this answer | |
  • Hi @jmh sorry I probably wasn't clear enough. I'm looking for a function that will do everything in one cell. That is, without referencing other cells. Also, unless I'm mistaken, I think DateDif only works with years, months, days. It won't give a result in hours. – Max MacLeod May 30 at 18:11
  • what are you trying to calculate that requires no reference cells? – jmh May 30 at 18:12
  • the elapsed time in hours between a time yesterday and another time today. – Max MacLeod May 30 at 18:14
  • if you have the result in days just multiply by 24 to get hours. You can make the line be 24*DATEDIF. – jmh May 30 at 18:16
  • You just put today's date in a cell and tomorrow's date in another cell then use those two cells as reference cells. – jmh May 30 at 18:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .