Formatting a tricky date.

[Bertalias]

A tricky one that I have been struggling with…

The following is a meteorological observation that currently sits wholly in A1 as a string:
EGNT 032320Z VRB02KT CAVOK 02/00 Q1022=

The second part of the observation is the date and time group where 032320Z refers to 03 April 23:20.

I am trying to turn this group into a valid date and time group that I can use with other observations for plotting, but have come across lots of problems with cell data formatting.

Ideally I would like to turn this into: dd/hh:mm like 03/23:20 or even better into the full string, e.g. dd/mm/yy hh:mm (03/05/11 23:20)

Many thanks if able to help,

Bert

 

[Bertalias]

I should have mentioned that I would like this done via a formula if possible.

 

[AusSteelMan]

Hi Bertalias,

I quickly put this together for your consideration

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 288px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 75px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>EGNT 032320Z VRB02KT CAVOK 02/00 Q1022=</TD><TD style="TEXT-ALIGN: right">2011</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">3/05/2011</TD><TD style="TEXT-ALIGN: right">11:20 PM</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D1</TD><TD>=DATE(B1,C1,MID(A1,6,2))</TD></TR><TR><TD>E1</TD><TD>=TIME(MID(A1,8,2),MID(A1,10,2),)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Since your data does not include year and month, you would need to provide this info: hence Cols B and C.
Col D has the Date
Col E has the Time

Note: It does assume that the date and time info in Col A always starts at the 6th character. If it doesn't you could use well known extraction methods the pull the 6 characters out into Col B and shift the rest over.

Maybe not the best solution, but depending on what you want to do with your data, the date and time are in seperate columns which may well be a significant advantage.
The Year and Month can be readily entered using an action such as Fill Down.

Regards,
ASM

 

[Bertalias]

Many thanks for your help.

By adapting your formulas, I have been able to get exactly what was required (see E2). I have also been able to account for the fact that as new observations are imported into Excel, the correct month should automatically appear in my list of 24 observations (C3).

Thanks,

Bert

Excel Workbook
	A	B	C	D	E
2	EGNT 041220Z 12006KT 060V170 CAVOK 13/02 Q1022=	4	5	2011	04/05/2011 12:20
3	EGNT 041150Z 18006KT 140V230 CAVOK 13/M01 Q1022=	4	5	2011	04/05/2011 11:50
Sheet4

 

[AusSteelMan]

Hi,

Glad it was some help for you, but just keep in mind the use of Today(). If you are still using the info next month, all the dates will flop over to June. This was my primary reason for not using Today().

Nice work in Col E. Keep in mind that it may make a chart axis quite busy looking if you have even a single day's worth of 30 minute interval data (48 points). In this case the date will be repeated for every data label, increasing visual conjestion, with no appreciable benefit.
I would think it would be the same for only 24 points. Even if not too conjested, if all the points are from the same day, labeling each data axis label is probably superfluous and the now the date could be just included in the Title of the chart.

Just a couple of thoughts.

Good luck
ASM