A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Charts and Charting
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Grahing Finish Times That go past Midnight



 
 
Thread Tools Display Modes
  #1  
Old May 6th, 2011, 07:00 PM
yelnocer yelnocer is offline
Member
 
First recorded activity by OfficeFrustration: Feb 2005
Posts: 12
Default 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?
  #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
  #3  
Old May 13th, 2011, 02:26 PM
yelnocer yelnocer is offline
Member
 
First recorded activity by OfficeFrustration: Feb 2005
Posts: 12
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?
Thank you - worked perfectly!!
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:41 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.