What is the best way to map a spreadsheet date formatted as 13-Jan 13-Feb... to use for reporting 1

[Pack10]

I will be working on a new project that revolves around either linking to a spreadsheet or importing the tabs as tables.
They want to be able to report on the numbers depending on the business unit or category selected.

My question is the columns are labelled 13-Jan 13-Feb 13-Mar etc... if i have a combo box that selects date range...how do I handle this. Do I need a table of that I can map that value to a from to ---- kind of link the dates and then have the from and to for that period. That would work, I think?

SSDate From To
13-Jan 01/01/2013 01/31/2013
13-Feb 02/01/2013 02/28/2013 ....

 

[SkipVought]

hi,

First of all, you need to verify that the ACTUAL value equals the DISPLAY value in any particular case. This can be done by selecting any particular value and changing the NUMBER FORMAT to GENERAL. If the DISPLAY value changes from, for instance, 13-Jan to 41287, then 13-Jan is the DISPLAY value for a DATE SERIAL VALUE of 41287, which is Jan 13, 2013. Of course the date COULD be some other year as well, but that would be returned by the YEAR(DATE SERIAL VALUE).

If these values are simply STRING values (in other words the value remains 13-Jan when you change the NUMBER FORMAT to GENERAL), then 13-Jan is just TEXT! Now you must determine by other means what YEAR this partial date string represents.

So you need to determine the answer to this before proceeding toward a solution.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Pack10]

Thanks for responding. Let me take a step back. The raw spreadsheet contains a merged column containing "2014" or "2015"
and 12 columns for the Months ie..
the original sheet looks like this. What is the best approach. Where I want to go is...
have a form in access with date range to select the appropriate records.

2014 2015
Jan Feb Mar Apr May Jun Jul Aug Sep .... Jan Feb Mar Apr May Jun Jul Aug...

 

[SkipVought]

merged column containing "2014" or "2015"

so if you have merged data (barf) does that mean that somewhere else you have other merged cells? (do you REALLY have merged COLUMNS??)

So what you have is
[pre]
+---------------------------------------------+---------------------------------------------+
| 2014 | 2015 |
+---------------------------------------------+---------------------------------------------+
| Jan | Feb | Mar | Apr | May | Jun |...| Dec | Jan | Feb | Mar | Apr | May | Jun |...| Dec |
[/pre]

So where is [highlight]13-Jan[/highlight]?

You still have not answered THAT original issue!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Pack10]

I actually created that value. Actually the row containing the year is merged, i manually created the 13-Jan eliminating the merged row containing the "2014"...so that never would make it into access. The original sheet, the months are text "Jan" "Feb"...
i don't have merged columns. Sorry for any confusion.

 

[MajP]

This is really unclear. Can you provide some more info?

columns are labelled 13-Jan 13-Feb 13-Mar

Jan Feb Mar Apr May Jun Jul Aug Sep .... Jan Feb Mar Apr May Jun Jul Aug

Are the columns in the form yy-mmm, or are they just months? You are saying two different things.
Are the rows days?
What would you expect to see in the combo? A list of days within a month? A list of months? Years?
What in your mind is a record.

 

[MajP]

I will take a stab here.
Somehow you want to provide a value in the form "YY-mmm" and you want to populate a combobox with the days in that month.

Public Sub PopulateComboByRange(Combo As Access.ComboBox, StrRange As String)
  Dim StartDate As Date
  Dim EndDate As Date
  Dim StrYear As String
  Dim StrMonth As String
  Dim DateCounter As Date
  StrMonth = GetMonthFromString(StrRange)
  StrYear = GetYearFromString(StrRange)
  StartDate = CDate("1/" & StrMonth & "/" & StrYear)
  EndDate = GetLastDayInMonth(StartDate)
  Combo.RowSourceType = "Value List"
  Combo.RowSource = ""
  DateCounter = StartDate
  Do While DateCounter <= EndDate
    Combo.AddItem (DateCounter)
    DateCounter = DateCounter + 1
  Loop
End Sub
Public Function GetYearFromString(StrRange As String) As String
  Dim aRange() As String
  aRange = Split(StrRange, "-")
  StrYear = Trim(aRange(0))
  If Len(StrYear) = 1 Then StrYear = "0" & StrYear
  StrYear = "20" & StrYear
  GetYearFromString = StrYear
End Function
Public Function GetMonthFromString(StrRange As String) As String
  Dim StrMonth As String
  Dim aRange() As String
  aRange = Split(StrRange, "-")
  StrMonth = Trim(aRange(1))
  GetMonthFromString = StrMonth
End Function
Public Function GetLastDayInMonth(FirstDayOfMonth As Date) As Date
    GetLastDayInMonth = DateAdd("m", 1, FirstDayOfMonth) - 1   'Days in month.
End Function
Private Sub Command2_Click()
  PopulateComboByRange Me.Combo0, InputBox("Date")
End Sub

If I pass in 14-Feb it will populate a combobox with values
2/1/2014
...
...
2/28/2014

 

[SkipVought]

Confusing!!!

Still have no idea what data you are ACTAULLY working with.

If you are creating this, please do NOT us anything by REAL DATES or you'll have to do handstands and back flips to get your application to work. USE REAL DATES whenever you have some date representation and then you can use a NUMBER FORMAT to make the DISPLAY Jan, for instance. But the underlying value would be 1/1/2014 or 1/1/2015!

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

BTW, your spreadsheet structure is not conducive to "linking to a spreadsheet or importing the tabs as tables" as your spreadsheet data is not normalized.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}