Excel Customer Service
April 27, 2014
I know that you cannot change a "time" field to a duration, but I have yet to figure out how to create a custom column that will do the same.
Attached is a sample csv file with Agent,period,Login Time as the fields. The Login Time is the actual time they were logged in during the period.
1:00:00 equals 1 hour (3600 seconds).
When I bring the data into Power Query it reads the Login Time as as time formatted field and displays as 1:00:00 AM, 0:51:34 reads as 12:51:34 AM. I've thought of adding a custom column with a time of 12:00:00 AM and subtracting it from the Login Time column. But, I cannot figure how to create this column (call it BaseTime). My final formula would look something like (Login Time - BaseTime) * 86400 (and then change that to a whole number).
So how can I create this BaseTime column or can I simply subtract 12 hours from the Login Time field. If so, please enlighten me on how to do so. Date/Time functions in M are my nemesis right now.