+ Reply to Thread
Results 1 to 5 of 5

Pivot Table Help calculating daily time

  1. 04-14-2020, 10:11 AM #1
    Derek B
    Derek B is offline
    Registered User
    Join Date
    08-01-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    11

    Pivot Table Help calculating daily time

    Hi Folks

    Is this possible please?

    I have a spreadsheet where the rows are automatically filled from a form when a staff member gets in to the office, goes and returns from lunch and clocks out in the evening.

    My questions is,

    Is it possible to combined the 4 entries from one day into a row, calculate the time worked from in the office till going home and remove the lunch time?
    (('On Lunch' - In the office') + ('Heading Home - Back from Lunch')) which would give a total of the daily hours worked?
    would I be best adding this calcs as a column? if so how do i combined the 4 rows to give a daily total?

    I hope I have explained my predicament?

    Sample of the data collected below.
    Thanks in advance,

    Derek.

    Capture2.PNG
    Attached Files Attached Files
    Last edited by Derek B; 04-14-2020 at 11:20 AM. Reason: sheet added
    Reply With QuoteRegister To Reply
  2. 04-14-2020, 11:03 AM #2
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,463

    Re: Pivot Table Help calculating daily time

    Administrative note

    Welcome to the forum

    in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us help you
    How to paste Power Query code ( by Olly)
    Reply With QuoteRegister To Reply
  3. 04-14-2020, 11:21 AM #3
    Derek B
    Derek B is offline
    Registered User
    Join Date
    08-01-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Pivot Table Help calculating daily time

    Sorry I have added the sheet.
    Reply With QuoteRegister To Reply
  4. 04-14-2020, 03:28 PM #4
    ThxAlot
    ThxAlot is offline
    Registered User
    Join Date
    01-24-2020
    Location
    Belgium
    MS-Off Ver
    2013
    Posts
    95

    Re: Pivot Table Help calculating daily time

    Hi, there,

    The answer to your question is YES.

    I tweak the mockup data in order to fit it in the pivot table; column "Date" is added as a filter in the pivot table.

    Calculated items in the pivot field Status are created as follows,
    'Duration in the Office' =IF(('In the Office' > 0 ) * ('Heading Home' > 0 ),'Heading Home' -'In the Office', NA() )
    'Lunch Break' =IF(('On Lunch' > 0 ) * ('Lunch Finished' > 0 ),'Lunch Finished' -'On Lunch', NA() )
    'Daily Work Hours' ='Duration in the Office' -'Lunch Break'

    Pls refer to the attched file for details.

    Cheers
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 04-16-2020, 05:15 AM #5
    Derek B
    Derek B is offline
    Registered User
    Join Date
    08-01-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Pivot Table Help calculating daily time

    ThxAlot thank you so much.

    I really appreciate that. Top class.

    Derek.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculating time (daily, weekly, monthly, yearly)
    By rayted in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-07-2017, 01:22 PM
  2. Calculating hours worked daily, weekly, monthly in a pivot/slicer
    By pini37 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-22-2016, 10:11 AM
  3. Calculate Daily, Weekly & Monthly Averages of daily Data not using Pivot Table
    By PWinkz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-01-2013, 05:05 AM
  4. [SOLVED] Find corresponding time for maximum daily values in a pivot table
    By BDCexcel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-19-2013, 03:13 PM
  5. [SOLVED] Calculating Fastest time in pivot table
    By Jonga21 in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 10-29-2012, 01:12 PM
  6. [SOLVED] Calculating daily time absence of multiple users, based on 8 hour schedule.
    By janeu in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 10-05-2012, 08:28 AM
  7. Daily Difference in Pivot Table
    By agentred in forum Excel General
    Replies: 2
    Last Post: 01-06-2010, 03:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1