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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Charting times in EXCEL in 24 hr basis without colon
I have downloaded a yearly chart of daily sun rise and sun set times from the Naval Observatory that shows the times on a 24 hour basis without a colon (i.e. 7:24 A.M., for instance, is shown as just 724). I would like to chart these times in EXCEL without having to manually insert a colon in all 732 cells --- I have tried handling it as a text string to separate the hours from the minutes and then insert the colon and re-combine but for some reason I cannot reformat to a time basis nor will it enter on a chart. Any ideas?
-- MBM |
#2
|
|||
|
|||
Charting times in EXCEL in 24 hr basis without colon
Try ...
If numbers are in col A, A1 down (between 0 to 2359) Put in B1: =(LEFT(TEXT(A1,"0000"),2)&":"&RIGHT(TEXT(A1,"0000" ),2))+0 Copy down Format col B as Time (say "13:30" format) -- Rgds Max xl 97 --- Please respond in thread xdemechanik atyahoodotcom ---- "Mutonbee" wrote in message ... I have downloaded a yearly chart of daily sun rise and sun set times from the Naval Observatory that shows the times on a 24 hour basis without a colon (i.e. 7:24 A.M., for instance, is shown as just 724). I would like to chart these times in EXCEL without having to manually insert a colon in all 732 cells --- I have tried handling it as a text string to separate the hours from the minutes and then insert the colon and re-combine but for some reason I cannot reformat to a time basis nor will it enter on a chart. Any ideas? -- MBM |
#3
|
|||
|
|||
Charting times in EXCEL in 24 hr basis without colon
If your set-up looks something like the sample set* below
assumed say, in Sheet1 in A1:E8 … Jan Jan Feb Feb … Rise Set Rise Set Day h m h m h m h m 1 751 1642 732 1720 2 751 1643 731 1722 3 751 1644 730 1723 4 751 1645 729 1725 5 750 1646 727 1726 *from: http://www.shusu.net/blog/suntimes.html In a new sheet, say, Sheet2 --------------------------------------- Copy paste over the headers in the first 3 rows and the "Day" col (col A) Put in B4: =(LEFT(TEXT(Sheet1!B4,"0000"),2)&":"&RIGHT(TEXT(Sh eet1!B4,"0000"),2))+0 Copy B4 across to E4, then copy down to E8 Format B4:E8 as time (in h:mm) (Click Format Cells Time select: "13:30" under Type) The above will convert the figs in B4:E8 to Excel's time format Use Sheet2 for your chart -- Rgds Max xl 97 --- Please respond in thread xdemechanik atyahoodotcom ---- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel 2000 crushed | Sue | General Discussion | 3 | June 30th, 2004 12:25 AM |
Charting a normal distribution within excel.. | Michael R Middleton | Charts and Charting | 1 | May 25th, 2004 06:01 PM |
Free books on Charting, Basics, Data Management, Functions; Free Add-In "Excel Usability Enhancer" | VJ | Setting up and Configuration | 0 | December 11th, 2003 09:01 AM |