Format Cells to calculate hours and minutes taken to complete task
 

Hi

I have the following calculation in my column E:
=((D3-C3)*24)-(NETWORKDAYS(C3,D3)*24)+24

Column D is Date/Time order completed in format: dd/mm/yyyy hh:mm:ss
Column C is Date/Time order received in format: dd/mm/yyyy hh:mm:ss

Currently my results in column E return 0.17 which equates to 10 minutes - I
really want it to show 10 minutes and not 0.17

Can anyone help?

Format Cells to calculate hours and minutes taken to complete task
 

Well, you could modify your formula so that the final result is
divided by 24 (ie putting it back into Excel time) and then format the
formula cell as an Excel time format ie

[h]:mm

(if tasks may take over 24 hours)

Alternatively, you could multiply by 60 in which case you will end up
with an integer value representing the number of minutes to complete
the task.

Hope this helps!

Richard

Format Cells to calculate hours and minutes taken to complete
 

Hi Richard

Grateful if you can tell me where in my formula I can make the changes you
suggest because if I put /24 at the end vice +24 and format cell to [h]:mm
then it returns 4:00

Any help would be greatly appreciated.

Thanks

Andy

"RichardSchollar" wrote:



Well, you could modify your formula so that the final result is
divided by 24 (ie putting it back into Excel time) and then format the
formula cell as an Excel time format ie

[h]:mm

(if tasks may take over 24 hours)

Alternatively, you could multiply by 60 in which case you will end up
with an integer value representing the number of minutes to complete
the task.

Hope this helps!

Richard

Format Cells to calculate hours and minutes taken to complete task
 

Microsoft times are a multiple of days. I would not multiply by 24 hours.

=((D3-C3))-(NETWORKDAYS(C3,D3))+1

The above formula will give you an answer in days. Then format the cell
using a time format which should give you an accurate hh:mm format.


"AndyO_UK" wrote:

Hi

I have the following calculation in my column E:
=((D3-C3)*24)-(NETWORKDAYS(C3,D3)*24)+24

Column D is Date/Time order completed in format: dd/mm/yyyy hh:mm:ss
Column C is Date/Time order received in format: dd/mm/yyyy hh:mm:ss

Currently my results in column E return 0.17 which equates to 10 minutes - I
really want it to show 10 minutes and not 0.17

Can anyone help?

Format Cells to calculate hours and minutes taken to complete
 

I think the right answer is very simple
=D3-C3 Then format using time format.


"Joel" wrote:

Microsoft times are a multiple of days. I would not multiply by 24 hours.

=((D3-C3))-(NETWORKDAYS(C3,D3))+1

The above formula will give you an answer in days. Then format the cell
using a time format which should give you an accurate hh:mm format.


"AndyO_UK" wrote:

Hi

I have the following calculation in my column E:
=((D3-C3)*24)-(NETWORKDAYS(C3,D3)*24)+24

Column D is Date/Time order completed in format: dd/mm/yyyy hh:mm:ss
Column C is Date/Time order received in format: dd/mm/yyyy hh:mm:ss

Currently my results in column E return 0.17 which equates to 10 minutes - I
really want it to show 10 minutes and not 0.17

Can anyone help?

Format Cells to calculate hours and minutes taken to complete
 

Joel

Excellent - works perfectly.....many many thanks

Regards

Andy

"Joel" wrote:

Microsoft times are a multiple of days. I would not multiply by 24 hours.

=((D3-C3))-(NETWORKDAYS(C3,D3))+1

The above formula will give you an answer in days. Then format the cell
using a time format which should give you an accurate hh:mm format.


"AndyO_UK" wrote:

Hi

I have the following calculation in my column E:
=((D3-C3)*24)-(NETWORKDAYS(C3,D3)*24)+24

Column D is Date/Time order completed in format: dd/mm/yyyy hh:mm:ss
Column C is Date/Time order received in format: dd/mm/yyyy hh:mm:ss

Currently my results in column E return 0.17 which equates to 10 minutes - I
really want it to show 10 minutes and not 0.17

Can anyone help?