cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Announcements
798
Views
0
Helpful
2
Replies
john.cunningham
Beginner

Embarassing question about time

On web tracking user reports, is the time time spent value hours:minutes or minutes:seconds? I have looked around a lot and cannot find this answer. My data is vague enough it is hard to determine. Also, is there an easy way in the CSV reporting to convert the large numeric value into human friendly time? Thanks much in advance.                   

1 ACCEPTED SOLUTION

Accepted Solutions
Puja Mahapatra
Cisco Employee

Hello John,

It is hours:minutes, You should be able to find the formula to convert epoch time to human readable time for excel.

Here is an example:

The formula for doing so is : =(((E2-(6*3600))/86400)+25569)

Where :

E2 = cell reference

6 = Timezone Offset (this is Central Standard time)

3600 = Number of seconds in an hour

86400 = Number of seconds in a year

25569 = Excel hack because excel counts epoch from 1/1/1900 and most others start at 1/1/1970.

After you have applied the formula , the output will be a large number to

convert it into human readable date and time select the column on which you

are applying the changes on, in this case the results of our formula->

select format->cells->time (select the format you wish to see).

Regards,

-Puja

View solution in original post

2 REPLIES 2
Puja Mahapatra
Cisco Employee

Hello John,

It is hours:minutes, You should be able to find the formula to convert epoch time to human readable time for excel.

Here is an example:

The formula for doing so is : =(((E2-(6*3600))/86400)+25569)

Where :

E2 = cell reference

6 = Timezone Offset (this is Central Standard time)

3600 = Number of seconds in an hour

86400 = Number of seconds in a year

25569 = Excel hack because excel counts epoch from 1/1/1900 and most others start at 1/1/1970.

After you have applied the formula , the output will be a large number to

convert it into human readable date and time select the column on which you

are applying the changes on, in this case the results of our formula->

select format->cells->time (select the format you wish to see).

Regards,

-Puja

View solution in original post

Puja, perfect, thank you so much. Last thing, in your experience how would you rate the accuracy of time spent numbers overall?