Hello All. I am working on a project to identify the amount of auxiliary time is used per day per employee. The data is presented in seconds. I have attached an example of the data and how is received it. Basically I receive the file every day and the data is added to the same worksheet to complete the week (Sunday through Saturday)at the completion of the week a new tab is created to commence the week and the process is the same all over again. I created a table the formula I used doesn’t work as when the date its change the formula returned an error. Obviously I did it wrong. L Is there a way to create a formula to translate the seconds into minutes and that it works when I change the date on the table? Thank you and happy Thanksgiving.
Formula to convert seconds to minutes
- Thread starter Yitzhackm
- Start date
NARAYANK991
Excel Ninja
Hi Yitzhack ,
I am confused by your data ; in which sheet / column / cells do you want the formula ?
Why should a formula which converts seconds to minutes not work when dates are changed ?
Narayan
I am confused by your data ; in which sheet / column / cells do you want the formula ?
Why should a formula which converts seconds to minutes not work when dates are changed ?
Narayan
Not that I am aware of directly
The issue is that You are displaying seconds as seconds, but Excel internally stores them as a fraction of a day
eg
1 day = 24 x 60 x 60 seconds = 86400 seconds
so
1 second = 1/86400
=0.000011574
In the PT B4 the value 3877 should be 3877/86400
You have a few options
1. Setup a side table where you transfer all your orginal data into helper cells
in AH4: =V4/86400
then use these new ranges as the source for your pivot table
2. Use Calculated Fields in teh Pivot table to derive new fields with the revised numbers
3. Link a revised summary table off the side of the Pivot table
=B4/86400
then form,at this as [hh]:mm
The issue is that You are displaying seconds as seconds, but Excel internally stores them as a fraction of a day
eg
1 day = 24 x 60 x 60 seconds = 86400 seconds
so
1 second = 1/86400
=0.000011574
In the PT B4 the value 3877 should be 3877/86400
You have a few options
1. Setup a side table where you transfer all your orginal data into helper cells
in AH4: =V4/86400
then use these new ranges as the source for your pivot table
2. Use Calculated Fields in teh Pivot table to derive new fields with the revised numbers
3. Link a revised summary table off the side of the Pivot table
=B4/86400
then form,at this as [hh]:mm
Somendra Misra
Excel Ninja
Hi Yitzhackm,
Please see the attached file. I had consider only 1 field for starting AUX 0.
I had understood that for any agent XXX on date DD/MM/YYYY value say 1000 under AUX 0 heading is in seconds and you want them in mins.
So I had inserted a field in your pivot table(Yellow Colour) which will give data in mins. If this what you want than the process can be repeated for other auxiliaries also.
Tell us if you want something different.
Somendra.
Please see the attached file. I had consider only 1 field for starting AUX 0.
I had understood that for any agent XXX on date DD/MM/YYYY value say 1000 under AUX 0 heading is in seconds and you want them in mins.
So I had inserted a field in your pivot table(Yellow Colour) which will give data in mins. If this what you want than the process can be repeated for other auxiliaries also.
Tell us if you want something different.
Somendra.
