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
| Attachment | Size |
|---|---|
| EmployeeTimes.xlsx | 53.19 KB |
| EmployeeTimes-Nick.xlsx | 11.43 KB |
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
e.g:
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
Nick
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.
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.