5

I have a formula for calculated column which gets the date difference in hh:mm format.

=TEXT([COLUMN2]-[COLUMN1],"h:mm")

But I want to get the difference in just minutes. I created very complex formula and they didn't work. For example:

=TEXT((TEXT(([COLUMN2]-[COLUMN1]),"h")*60)+(([COLUMN2]-(TEXT(([COLUMN2]-[COLUMN1]),"h")*60))-[COLUMN1]))

Please help me about getting the difference in minutes.

The result that I expect:

1/15/2014 16:35 1/15/2014 16:45 The result should be 10.

1/15/2014 7:00 1/15/2014 7:00 The result should be 0.

Improve this question
3
  • Did you try what I suggested? I updated my answer to show that it works exactly as you expect
    – MdMazzotti
    Commented Jan 16, 2014 at 10:25
  • Ow I'm so stupid. I forgot to change the return type as number from date time format. It didn't work with the 1440. but what I wrote is worked. Thank you anyway.
    – e.ozmen
    Commented Jan 16, 2014 at 10:27
  • I'm totally stumped. Anyhow, good that it works now
    – MdMazzotti
    Commented Jan 16, 2014 at 10:30

4 Answers 4

Reset to default
2

According to this link it should be easy.

Try this:

=([COLUMN2] - [COLUMN1])*1440

This will return the total number of minutes between the two dates

UPDATE

enter image description here

Improve this answer
10
  • It looks like it should work correct. But gives me this kind of results 12/20/1899 0:00.
    – e.ozmen
    Commented Jan 16, 2014 at 10:05
  • What data type did you set for the calculated field? It must be a number not a date
    – MdMazzotti
    Commented Jan 16, 2014 at 10:06
  • It's date. Not number. 1/15/2014 21:30. So what should I do?
    – e.ozmen
    Commented Jan 16, 2014 at 10:06
  • 1
    The formula returns a number. The data type for the calculated column must be a number. How could a number representing a time span in minutes be converted to a date?
    – MdMazzotti
    Commented Jan 16, 2014 at 10:08
  • 1
    I forgot to mark it as answer. Your formula worked. Sorry!
    – e.ozmen
    Commented Jan 27, 2014 at 6:24
0

Try with the following formula in the calculated column,

=MINUTE([Column2]-[Column1])

the above code returns Minutes between two times.

for more information, please refer the following link,

Calculated Field Formulas

Improve this answer
4
  • It always gives 5 or for the 0 difference 12/30/1899 0:00 or 1/9/1900 0:00.
    – e.ozmen
    Commented Jan 16, 2014 at 9:57
  • this formula returns the difference in the minutes unit between two times, not the total number of minutes between the two dates
    – MdMazzotti
    Commented Jan 16, 2014 at 9:58
  • Sorry for the uncleared explaination. Yes I want the total number of minutes of the difference.
    – e.ozmen
    Commented Jan 16, 2014 at 10:01
  • @e.ozmen see my answer then
    – MdMazzotti
    Commented Jan 16, 2014 at 10:04
0 
=[COLUMN1]+([COLUMN2]/1440)

i think the key here is to get the full ammount of minutes in a day, col 1 is the start date and col 2 end date

EDIT

this should work!

=INT(([Column2]-[Column1])*1440)

above was taken from link below but converted to work in sharepoint!

06/09/2007 10:35 AM vs 06/10/2007 3:30 PM

=INT((Column2-Column1)*1440)

Total minutes between two times (1735)

http://office.microsoft.com/en-gb/windows-sharepoint-services-help/examples-of-common-formulas-HA001160947.aspx

Improve this answer
2
  • But how can this get the difference ?
    – e.ozmen
    Commented Jan 16, 2014 at 10:01
  • just updated my answer!
    – Ali Jafer
    Commented Jan 16, 2014 at 11:17
0

Try this:

Hours Calculation:

=([COLUMN2] - [COLUMN1])*24

This will return the total number of hours between the dates

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.