Conditional Formula for Dates/Formatting Answer as Time Format

[JenMcG]

Hello,

I currently use an excel spreadsheet to calculate the difference between 2 dates (K6=Received Date and L6 Date contacted). The format of the dates is as follows: 10/20/2017 12:12:49 PM

The conditional formula I am using is: =IF(K6="","",IF(L6="",NOW()-K6,L371-K6))

Currently the answer is displayed as a fraction, but I would like it converted to minutes. I thought it would be easy enough to change the cell format but that is not providing the answer I require.

This measured is so critical to the process it needs to be the minute.

How can I achieve this?

Example of current state is below:
[TABLE="width: 50"]
<tbody>[TR]
[TD="width: 32%, bgcolor: #003366"]Received Date

[/TD]
[TD="width: 38%, bgcolor: #003366"]Contacted Date

[/TD]
[TD="width: 30%, bgcolor: #FF0000"]MINUTES FROM RECEIVED TO CONTACTED

[/TD]
[/TR]
[TR]
[TD="width: 32%"]10/20/2017 12:12:49 PM

[/TD]
[TD="width: 38%"]10/20/2017 12:18:46 PM

[/TD]
[TD="width: 30%"]0.00

[/TD]
[/TR]
</tbody>[/TABLE]


Thank you for your help!

JM

 

[barry houdini]

I'm not quite sure why you use L371 in the formula, shouldn't that be L6?

To show the result as minutes and seconds custom format cell as m:ss - for your example that should give the result 5:57 - is that the result you want

 

[JenMcG]

Hello Barry,

Your correct. it should be L6- Typo on my part (I have the spreadsheet filtered, copied the formula over and for to change that one for the purpose of the post).

The custom format works splendid. Thank you. I was using the time setting and didn't think to try the custom field.

Much appreciated!

JM