Use excel to calculate the time and number of days between two dates imported from Outlook

[Walband]

Good day all
Background

I work for a small design consulting house. We send sales people to meet with clients and then get a design brief from the client. Once the sales representative returns to the office he will fill out a folder and assign each brief a unique code. the folder will contain the detailed hand drawing, the client details and any other information that is needed.

The sales representative then e-mails the technical department, and tells them that they have a file for collection. After finishing the technical aspect of the folder they then send an e-mail to the next department, telling them to collect the file.

The file moves from the Sales representative, to technical, to procurement, to finance, to sales support and finally back to the sales representative, completing the cycle.

I need to track the amount of time that the file spends in each department using the date and time stamp on the outlook e-mail.
Currently we do not have any system in place and I have been copying suggestions in other threads, but none seem to work for my needs:
1. the date and time stamp in Outlook does not seem to be compatible with the date format in Excel?
2. I need to take working hours (08:00-17:00) and weekends into account.

I am not an advanced or expert user, but do understand the basics of formulas etc.

Any advice and or help will be greatly appreciated.
Regards

[zbor]

Can you upload example workbook?

make sure to hide all private and confidential data.

[Walband]

Proposed Blue file tracker.xlsx
Hi zbor, thanks for the reply. I hope that I have attached correctly (as mentioned before - I am a bit of a technophobe )

[Walband]

Bump to the top.....
I have been told that this can not be done.
Any takers before I close off?

[Pete_UK]

The date/times you have in K3 and L3 of your example are in text format, so you will first have to convert those into Excel format. This formula will convert K3 into a date/time that Excel recognises:

=DATEVALUE(MID(K3,5,LEN(K3)-13))+VALUE(MID(K3,16,5)&":00")+IF(RIGHT(K3,2)="PM",0.5,0)

and the following will do the same to L3:

=DATEVALUE(MID(L3,5,LEN(L3)-13))+VALUE(MID(L3,16,5)&":00")+IF(RIGHT(L3,2)="PM",0.5,0)

Perhaps you can make use of these in a NETWORKDAYS formula (I'm not sure if that takes account of times).

Post back if you need further help.

Hope this helps.

Pete