#1
|
|||
|
|||
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? |
#2
|
||||
|
||||
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] |
#3
|
|||
|
|||
Ok, how can I do that?
|
#4
|
||||
|
||||
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] |
#5
|
|||
|
|||
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]). |
#6
|
||||
|
||||
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] |
#7
|
|||
|
|||
That works.
Thanks for help! |
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 cell format help!!! | Raner | Excel | 4 | 06-17-2013 11:13 PM |
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 |
Format Excel to look like Word | Kilconey | Excel | 1 | 04-30-2010 09:42 AM |