+ Reply to Thread
Results 1 to 6 of 6

Excel chart for hours worked

  1. #1
    Registered User
    Join Date
    01-29-2024
    Location
    Amsterdam
    MS-Off Ver
    365
    Posts
    6

    Excel chart for hours worked

    I don't use Excel charts often, but this chart is becoming a real challenge. I want to create a simple stacked column chart displaying the hours worked per day based on a table with date/time data. So far this was unsuccessful and none of the examples I found online offer this feature.

    X-axis are the times 00:00 to 24:00
    Y-axis are the dates in one month

    I included a sample table with the values of one month.

    This chart is intended to display the non worked periods in a day. Thus every day, the complete time of a day (0-24) should be displayed, and the non worked hours should be empty in a column. I wonder if this is even possible, or should I first create a calculation for the non worked periods and push both non worked and worked hours in a temporary table and create the chart from there?

    Art.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,545

    Re: Excel chart for hours worked

    Added two columns:
    time worked:
    Please Login or Register  to view this content.
    total time not worked:
    Please Login or Register  to view this content.
    added pivot table and chart
    Attached Files Attached Files
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    01-29-2024
    Location
    Amsterdam
    MS-Off Ver
    365
    Posts
    6

    Re: Excel chart for hours worked

    Hi Ben. Thx for your help, although I believe I had to be more specific.

    In my case I want to create a chart displaying the actual times worked. For example, on the 2nd of January from 15:11 until 16:31 and from 14:58 until 15:03. Thus 00:00 until 14:57, 15:04 until 15:10 and 16:32 umtil 23:59 are blank columns on that day. Dates where there were no working hours should be displayed as well but empty (my goal is to display a complete month in a chart).

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,301

    Re: Excel chart for hours worked

    Perhaps a Gantt chart would do what you want.
    The formula that fills in the time slots worked is: =IFERROR(AGGREGATE(15,6,(ROW(Tabel1[[start]:[start]])-ROW(Tabel1[#Headers]))/(Tabel1[[start]:[start]]<=SUM($E3,F$2))/(Tabel1[[end]:[end]]>=SUM($E3,F$1)),1),"")
    Both fill and font are conditionally formatted.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    01-29-2024
    Location
    Amsterdam
    MS-Off Ver
    365
    Posts
    6

    Re: Excel chart for hours worked

    Thx, that formula did the trick. After some modification and a VBA procedure I managed to display different time intervals as well.
    Attachment 859292

    The VBA code is essential if you want to switch between the time intervals. I put mine in my personal macro file (personal.xlsb). The command buton in the chart sheet must be linked to this macro.

    Please Login or Register  to view this content.
    Thx all for your help!
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,301

    Re: Excel chart for hours worked

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 8
    Last Post: 09-30-2017, 07:00 PM
  2. Replies: 4
    Last Post: 02-12-2013, 07:01 AM
  3. Replies: 2
    Last Post: 02-11-2013, 02:26 PM
  4. Replies: 0
    Last Post: 05-14-2012, 05:36 PM
  5. Calculating basic hours worked, between a time range and premium hours worked
    By RoyLittle0 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-07-2012, 06:59 AM
  6. Replies: 0
    Last Post: 01-05-2012, 06:23 AM
  7. [SOLVED] How to create a bar chart of hours worked by day?
    By mandruss in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 05-12-2005, 08:06 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1