4

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.

  • Did you try what I suggested? I updated my answer to show that it works exactly as you expect – MdMazzotti Jan 16 '14 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 Jan 16 '14 at 10:27
  • I'm totally stumped. Anyhow, good that it works now – MdMazzotti Jan 16 '14 at 10:30
1

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

  • It looks like it should work correct. But gives me this kind of results 12/20/1899 0:00. – e.ozmen Jan 16 '14 at 10:05
  • What data type did you set for the calculated field? It must be a number not a date – MdMazzotti Jan 16 '14 at 10:06
  • It's date. Not number. 1/15/2014 21:30. So what should I do? – e.ozmen Jan 16 '14 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 Jan 16 '14 at 10:08
  • 1
    I forgot to mark it as answer. Your formula worked. Sorry! – e.ozmen Jan 27 '14 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

  • It always gives 5 or for the 0 difference 12/30/1899 0:00 or 1/9/1900 0:00. – e.ozmen Jan 16 '14 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 Jan 16 '14 at 9:58
  • Sorry for the uncleared explaination. Yes I want the total number of minutes of the difference. – e.ozmen Jan 16 '14 at 10:01
  • @e.ozmen see my answer then – MdMazzotti Jan 16 '14 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

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

Try this:

Hours Calculation:

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

This will return the total number of hours between the dates

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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