Asif Faleel
asked on
Excel 2013 how to Get the earliest time per day user wise
Hi,
I have a requirement for getting the earliest time a user has entered the office on a per day basis. Is there a pivot that can do the same. Attached is the sample excel sheet.
Please help.
USer-Attendece-.xlsx
I have a requirement for getting the earliest time a user has entered the office on a per day basis. Is there a pivot that can do the same. Attached is the sample excel sheet.
Please help.
USer-Attendece-.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@Rgonzo - not quite Snap!! Near enough for military work though!!
@GulfIT - both solutiuons have added a column to the data to separate the date from the "date & time" value. We have then formatted the resulting pivot differently.
@GulfIT - both solutiuons have added a column to the data to separate the date from the "date & time" value. We have then formatted the resulting pivot differently.
Use MIN instead of Count in Pivot
Here's how:
1. On Column F use the formula: =TEXT(A2,"mmm-dd")
2. label this column as MonthDay
3. On Pivot Table settings, drop the User and MonthDay to Row Labels box
4. Then Drop the Date into the Values box, setting it from Count to Min
5. On the Pivot display, set the cell format of Date (Min of Date) to Time format.
Hope this helps
Here's how:
1. On Column F use the formula: =TEXT(A2,"mmm-dd")
2. label this column as MonthDay
3. On Pivot Table settings, drop the User and MonthDay to Row Labels box
4. Then Drop the Date into the Values box, setting it from Count to Min
5. On the Pivot display, set the cell format of Date (Min of Date) to Time format.
Hope this helps
@Rob
Your answer didn't show yet and I wasn't sure what the author ultimately wanted
Your answer didn't show yet and I wasn't sure what the author ultimately wanted
ASKER
Thanks that worked.
Is that what you are looking for?
Regards
USer-AttendeceV1.xlsx