Calculate Differences Between Time w/ Variables?

Hi All,

I really would appreciate any sort of help anyone can give me. I will try to explain in detail as best as possible. I am keeping track of user time that an employee uses a program. The variables I am working with is Log In, Log Off, Reconnect, and Disconnect. I figured out how to calculate the difference in time from an easy Log In to a Log Off, but the situation gets a lot more complicated making it much more confusing. I have tried using pivot tables but does not do what I need.

Again I need to find the total time each Employee is using the system. I do this process manually everyday which takes too long.

If you need more information please let me know.

I have a sample attached below.

Thank you in advance!

~ Mark

EmployeeTimes.xlsx53.19 KB
EmployeeTimes-Nick.xlsx11.43 KB
Nick's picture

to calculate time taken, you

to calculate time taken, you need only to subtract 2 date/times (that Excel understands) and format the result how you want

a1: 01-jan-2012 23:00:00
a2: 01-jan-2012 22:00:00

=A2-A1=1/24 (1 hour)

the times you have are not readable by Excel:
1/11/2012 3:31pm is understood as text

- recommend changing the formatting of the time you are saving to dd-mmm-yyyy hh:mm:ss, then you can import to Excel


Hi Nick, Thank you for your

Hi Nick,

Thank you for your response. I do understand how to change the date/time format into 24 hour format and subtract the differences. That gives me the time in between. If you take a look at my example I uploaded, you will see what I am talking about. I wish it was that easy.

Nick's picture

Mark - the best solution is

Mark - the best solution is to fix the root cause which is the bad date format from the log file..

If you're not able to do that, I have attached a solution for you that converts the date you have into an Excel-usable date.