View Single Post
  #2  
Old May 10th, 2011, 09:03 AM
tarquinious tarquinious is offline
Experienced Member
 
First recorded activity by OfficeFrustration: Mar 2011
Posts: 34
Default

Quote:
Originally Posted by yelnocer View Post
I need to produce a graph showing completion times. Problem is that some of the deadlines are after midnight. So finish times could be 23:30, 00:12. 22:50, 00:30 and 01:30. Is there a way to bar grah those values so that the times after midnight show "higher" than the times before midnight?
I believe the issue may be because, although your cells contain times, Excel is also taking into account the date (all time or date values contain both the time and date, but only display what you want to show).

Because your times after midnight still contain the same date as those times before midnight, the before midnight times are, in fact, a later time than the after midnight times (e.g. "15/03/2011 23:30" is later in the day than "15/03/2011 00:30").

To get around this, you could either view the cells with the date in and manually adjust all of the times after midnight to the day after, or use the formula below to do it for you:
Code:
=IF(B4-INT(B4)0.5,B4+1,B4)
NB: Please note, there is a less-than symbol before the "0.5" value in the formula above - this forum drops those symbols

Assuming your first time is in cell B4, paste this formula next to that time and copy down next to all of the time values. What this does is looks at the "time" portion of the date/time value and if it is before noon, adds 1 to the date to make it the next day.

May not be completely accurate for the data you are holding, however when graphed it appears the way you want it - i.e. times after midnight give a longer bar.

Last edited by tarquinious : May 10th, 2011 at 09:05 AM. Reason: forum drops greater-than and less-than