karinos57
asked on
How to calculate when system is down between datestamp
Hi,
i want to calculate when duration is zero based on the datestamp and would like to group it by Date and Hour. Pls. see attached sample for the desired results. thanks
sample.xlsx
i want to calculate when duration is zero based on the datestamp and would like to group it by Date and Hour. Pls. see attached sample for the desired results. thanks
sample.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
teylyn:
this is working great but one quesiton for you, where did you get the Days field in the pivot? i don't see it in the data source. I only see DateStamp. thanks
this is working great but one quesiton for you, where did you get the Days field in the pivot? i don't see it in the data source. I only see DateStamp. thanks
ASKER
i like the way you formatted like the DateStamp by just showing hours and grouped by a day but it is not clear. Can you post antother sheet where i can see all the formatting or if you can explain - i will appreciate that. thanks
ASKER
thnx
Hello,
I used Excel 2010 to create the pivot table. Right-click on any date in the pivot table and select Group - then tick the day and hour.
After that, I used the buttons on the pivot table layout tab to play with the appearance of the table. Some of these options are new to 2010, but I don't think I used any of the new ones.
cheers, teylyn
I used Excel 2010 to create the pivot table. Right-click on any date in the pivot table and select Group - then tick the day and hour.
After that, I used the buttons on the pivot table layout tab to play with the appearance of the table. Some of these options are new to 2010, but I don't think I used any of the new ones.
cheers, teylyn
=IF(C2=0,1440*(A1-A2),0)
cheers,