Charting times in EXCEL in 24 hr basis without colon

[Mutonbee]

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?

 

[Max]

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 <at>yahoo<dot>com
----

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?

 

[Max]

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(Sheet1!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