VBA Data Label FormatMar 21, 2008
I have a plot that shows the position of a drifting buoy in the ocean. I have created a second series that is just the most recent position of that buoy. Everyday, using VBA, I update the chart 2nd series to point to the location of the new lat and long. For this second series, which is only one point, I have data labels turned on. I replace the data label text with the date and time of that latest position. Here is my problem. I can not find a way to format the date. No matter what I try, it has the format mm/dd/yyyy hh:mm AM/PM. I really just want it to be a 24 hour clock (mm/dd/yy hhmm).
I have tried forcing the format of the variable date_of_latest_pos, I have tried changing the cell format that contains the actual date and time...
ActiveChart.SeriesCollection("Latest Position").ApplyDataLabels AutoText:=True, LegendKey:= False, ShowSeriesName:=True, ShowCategoryName:=False, ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False
Selection.Characters.Text = date_of_latest_pos
Selection.AutoScaleFont = False
With Selection.Characters(Start:=1, Length:=23).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic