Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-04-2011, 05:42 AM
Sammael Sammael is offline Excel 2007 and data in dd:hh:mm:ss format Windows 7 64bit Excel 2007 and data in dd:hh:mm:ss format Office 2007
Novice
Excel 2007 and data in dd:hh:mm:ss format
 
Join Date: Nov 2011
Posts: 8
Sammael is on a distinguished road
Default Excel 2007 and data in dd:hh:mm:ss format

I am using Excel 2007.



I want to draw chart of data that is in dd:hh:mm:ss format. But when I add those cells to chart not is shown on it. I am using scatter/x,y chart.

I wonder if Excel isn't recognising the dd:hh:mm:ss format. I didn't find it in standard time-formats so I made custom format for it. Or maybe Excel anyway thinks that those cells are text, not time?

How could I make Excel to understand this data? Or how could I convert it to some other form that Excel could use?
Reply With Quote
  #2  
Old 11-05-2011, 02:49 AM
macropod's Avatar
macropod macropod is offline Excel 2007 and data in dd:hh:mm:ss format Windows 7 64bit Excel 2007 and data in dd:hh:mm:ss format Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi Sammael,

Your 'dd:hh:mm:ss' data aren't a standard format format - you need to either dispense with the 'dd:' part or add the 'mm' & 'yyyy' parts, formatted like 'dd/mm/yyyy hh:mm:ss', for example.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 11-05-2011, 04:23 AM
Sammael Sammael is offline Excel 2007 and data in dd:hh:mm:ss format Windows 7 64bit Excel 2007 and data in dd:hh:mm:ss format Office 2007
Novice
Excel 2007 and data in dd:hh:mm:ss format
 
Join Date: Nov 2011
Posts: 8
Sammael is on a distinguished road
Default

Ok, how can I do that?
Reply With Quote
  #4  
Old 11-05-2011, 05:19 AM
macropod's Avatar
macropod macropod is offline Excel 2007 and data in dd:hh:mm:ss format Windows 7 64bit Excel 2007 and data in dd:hh:mm:ss format Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi Sammael,

For a 'dd:hh:mm:ss' string in A1, the following formula will convert the 'hh:mm:ss' portion to a timevalue:
=TIMEVALUE(RIGHT(A1,LEN(A1)-FIND(":",A1)))
or, if you also have different days for some of the times:
=TIMEVALUE(RIGHT(A1,LEN(A1)-FIND(":",A1)))+LEFT(A1,FIND(":",A1)-1)
Once you've done that for all the cells, you should be able to use the column containing the timevalues in your chart.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 11-05-2011, 06:15 AM
Sammael Sammael is offline Excel 2007 and data in dd:hh:mm:ss format Windows 7 64bit Excel 2007 and data in dd:hh:mm:ss format Office 2007
Novice
Excel 2007 and data in dd:hh:mm:ss format
 
Join Date: Nov 2011
Posts: 8
Sammael is on a distinguished road
Default

Excel wont accept those formulas. Says that there is error in them.
I changed A1 to correct cell in my sheet.

When giving error Excel bolds the following part of of the code in tools tip that appears: RIGHT(text;[num_chars]).
Reply With Quote
  #6  
Old 11-05-2011, 06:23 AM
macropod's Avatar
macropod macropod is offline Excel 2007 and data in dd:hh:mm:ss format Windows 7 64bit Excel 2007 and data in dd:hh:mm:ss format Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi Sammael,

That suggests your PC's regional settings are configured to use ';' where I've used ','. Try:
=TIMEVALUE(RIGHT(A1;LEN(A1)-FIND(":";A1)))
or, if you also have different days for some of the times:
=TIMEVALUE(RIGHT(A1;LEN(A1)-FIND(":";A1)))+LEFT(A1;FIND(":";A1)-1)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 11-05-2011, 06:52 AM
Sammael Sammael is offline Excel 2007 and data in dd:hh:mm:ss format Windows 7 64bit Excel 2007 and data in dd:hh:mm:ss format Office 2007
Novice
Excel 2007 and data in dd:hh:mm:ss format
 
Join Date: Nov 2011
Posts: 8
Sammael is on a distinguished road
Default

That works.

Thanks for help!
Reply With Quote
Reply

Tags
date, excel 2007, time

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to create microsoft Excel 2007 and 2010 File in .xlsx format ipacesetters Excel 1 12-16-2019 05:13 PM
Excel 2007 and data in dd:hh:mm:ss format Excel 2007 cell format help!!! Raner Excel 4 06-17-2013 11:13 PM
Excel 2007 and data in dd:hh:mm:ss format Excel 2007 data question Paul-NYS Excel 3 09-26-2011 10:49 PM
How to restore original data format - from a .pdf file to Excel columns KIM SOLIS Excel 1 09-18-2011 10:49 PM
Excel 2007 and data in dd:hh:mm:ss format Format Excel to look like Word Kilconey Excel 1 04-30-2010 09:42 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:22 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft