OfficeFrustration

OfficeFrustration (http://www.officefrustration.com/index.php)
-   Charts and Charting (http://www.officefrustration.com/forumdisplay.php?f=8)
-   -   Grahing Finish Times That go past Midnight (http://www.officefrustration.com/showthread.php?t=1124715)

yelnocer May 6th, 2011 07:00 PM

Grahing Finish Times That go past Midnight
 
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?

tarquinious May 10th, 2011 09:03 AM

Quote:

Originally Posted by yelnocer (Post 3749928)
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.

yelnocer May 13th, 2011 02:26 PM

Quote:

Originally Posted by yelnocer (Post 3749928)
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?

Thank you - worked perfectly!!


All times are GMT +1. The time now is 11:55 AM.

Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
OfficeFrustration.com