Solved

Calcalate hours active

Posted on 2013-12-17
8
134 Views
Last Modified: 2013-12-18
Hi Experts,

I have a spreadsheet which contains a (dynamic) value for "hours worked".  From this value (let's say 12) I want to plot a graph showing the hours worked, against all 24 hours in a day.

The middle of the shift is always 01:00AM

So, if hours worked = 12 the graph should show that the shift began at 19:00 and finished at 07:00.

It should be assumed that 1am always represents the middle of the shift.  I only want to show whole hours if possible (i.e. I don't want the graph to have to show 30 minute slots, caused by dividing the shift lenght by 2), but this is not a show-stopper.

Help!!!!

Thanks :)
0
Comment
Question by:Codestone
  • 4
  • 3
8 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
You can try a stacked bar or stacked column chart showing a transparent bar or column for the the off hours and a colored one for the working hours.
0
 
LVL 1

Author Comment

by:Codestone
Comment Utility
Sorry if I wasn't clear,

I know how to generate a graph, I'm trying to calculate the hours worked.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
If A1 contains hours in "time" format then you can use

=25/24-INT(A1*12)/24
0
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 500 total points
Comment Utility
HI,

pls try

TimeEE20131217.xlsx
Regards
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 1

Author Closing Comment

by:Codestone
Comment Utility
Legend,

Thanks!
0
 
LVL 1

Author Comment

by:Codestone
Comment Utility
Sorry, so thanks for this - I now know the start and end times of the shift.

Can you show me how to plot this?
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
Did my formula not work or you did not try it?
0
 
LVL 1

Author Comment

by:Codestone
Comment Utility
I couldn't get it to work, no.

THanks.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now