Calculating duration

[CornfedCreature]

Hi there,

I currently sitting in office stumbling over myself trying to put a formula in this report for my boss. Im guessing the deal was, he couldn't figure it out so has thrown the grenade my way.

I could really do with some credit by fixing this for him so any help you beautiful people can provide, the better!

In a nut shell, I have 2 fields, with 2 dates & times and I need to work out the duration in minutes.

eg:
[TABLE="width: 500"]
<tbody>[TR]
[TD]RAISED[/TD]
[TD]ASSIGNED[/TD]
[TD]Assignment Time (mins)[/TD]
[/TR]
[TR]
[TD][TABLE="width: 126"]
<tbody>[TR]
[TD="width: 126"]21/10/13 3.01pm[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 126"]
<tbody>[TR]
[TD="width: 126"]21/10/13 3.06pm[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Formula?[/TD]
[/TR]
</tbody>[/TABLE]


If there is anyone out there who can help I would be a truly grateful Aussie.

Thanks all in advance,
RF

 

[martindwilson]

is that a real date time or text?
try
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,".",":"),"pm"," pm"),"am"," am")-SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".",":"),"pm"," pm"),"am"," am")
format cell as [m]

 

[CornfedCreature]

It's a system generated report - the fields don't seem to have a date time format, its set to general. So text maybe?

 

[shg]

Did you try Martin's suggestion, or are you speculating?

 

[CornfedCreature]

Oh wait I missed the actual suggestion, (juggling work, employees and social media)

That worked a treat! Thank you so much Martin! You truly are an excel sorcerer

 

[CornfedCreature]

is that a real date time or text?
try
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,".",":"),"pm"," pm"),"am"," am")-SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,".",":"),"pm"," pm"),"am"," am")
format cell as [m]

Hey Martin,

I was hoping I could ask you another question related to this.

If I wanted to limit daily activity time frames from 8.30am - 5.00pm, how would I edit your formula?

The reason behind this is an employee will only operate within these hours so calculating the time outside these hours gives me inaccurate performance data.

Thanks again in advance. Considering this is the first time Ive joined one of these forums I am overwhelmed by the speed and content you have provided.

Cheers,
RF