How to convert months in year and month format Results

Hello. This website has helped me immensely, although there is one problem I can't seem to figure out. I'm trying to calculate 3 conditions which comprise of a location, month and year. I know how to get data from one worksheet or file and have the results show up in another worksheet and different file.

We have 4 locations; McKeesport, Homestead, Pittsburgh and Tarentum. We also have various applicants coming in to each center. I want to set it so that one cell checks for a center, then sees the month and year the person came in, then tally it up. So far we have the date in a MM/DD/YYYY format, but I think I'll just have to separate it manually either converting the date to text with the month being in one cell, and the year in another. But I want to be able to tally the number of mckeesport applicants in january 2007, the mckeesport applicants for feb 2007, tarentum applicants from jan 2007, etc. I've tried the COUNTIF function (didn't fit my needs), and nesting IF as well as AND functions, but the formula seems to come out incorrect.

This isn't extremely important, but it will cut down on two other people using the same sheet from having to count the entries manually. If it can be done I'd be most appreciative of the formula used that would make this calculation possible. Thank you for your time.

Eric B.

All,

when I try to reformat dates that are in a yyyymm format (200510) into a
mmm-yyyy format (Oct 2005) the conversion doesn't come close and I end up
with dates from the year 2048 amongst others. Any thoughts on how to fix
this? I am trying to apply it to a formula that will allow me to sum the
follwing:

=SUM((RawData!$CT$2:$CT$2000=$B3)*(RawData!$A$2:$A$2000=$C3)*(RawData!$AL$2:$AL$2000>0)*(RawData!$AL $2:$AL$2000<7))

where I can use a > Oct of 2005 and a < May of 2006 to track how many people
have completed a test in that time frame... but it's not working for me. The
>0 and <7 are being used to count how many months old the test is but I need
to start from the beginning of the Fiscal Year.

THanks.

When I export my files onto text and open it with Excel, it will automatically format the cells to take out leading zeros, convert to scientific notation, and convert certain numbers into date/month/year format. How can I get Excel to open my file without reformatting the numbers in my text file?

Thanks
Ocean

Hi There!

I have a spreadsheet of 55,000 rows each of which contains a date in the format dd/mm/yyyy and I would like to create a column which contains yyyy/season. So far I can get excel to convert a cell into a season given the relevant date but I can't figure out how to append the year in front of the season. This is what I'm using to convert month to collelating season: =IF(MONTH(B2)<2,"WINTER",IF(MONTH(B2)<5,"SPRING",IF(MONTH(B2)<8,"SUMMER",IF(MONTH(B2)<11,"AUTUMN",IF (MONTH(B2)<=12,"WINTER","NONE")))))

Thanks!!

Nat

A few weeks ago I created a thread outlining a large project I wanted to complete, as it turns out I realize I was asking for way too much,so for a novice I have spent quite some time putting together segments of code (after having researched) and then applying what is relevant to my project.Basically from a Userform in excel,parameters are defined by the user via a loaded combo box , the sql query is executed and the data returned to excel.This project involves building a report based on multiple SQL queries (only two added at this point).
I had the code working yesterday, but found it quite slow , I then made some changes to how the record set works and encountered 'one or more acessor flags' error followed by a timeout.
I cannot undo what I have done
Because the code is somewhat of a compilation it could be made more efficient and obvious errors pointed out-this is of course where the you helpful good minds come in.
Below is the code.


	VB:
	
 
 
 'Connection to SQL on KOSMO
 
Const stCon    As String = " ODBC;Description=KOSMO;DRIVER=SQL Server;SERVER=MERLIN;DATABASE=KOSMO;Trusted_Connection=Yes" 
 
Private Sub cmdClose_Click() 
     'Closes the form
    Unload Me 
End Sub 
 
 'Excecutes Report using loaded Queries and User selected Parameters
Private Sub cmdReport_Click() 
    Dim ws As Worksheet 
     
    Set ws = Worksheets("Exceptions") 
     'ws.Range("A1", "IV65000").Clear
    ws.Cells.Clear 
    ws.Cells.ClearFormats 
    Call InsertHeader 
    Call FindNextCell 
    Call LoadReceiptsModZero 
    Call FindNextCell 
    Call LoadRentalFeesModZero 
    ws.Range("A1").Select 
    Unload Me 
End Sub 
 
Private Sub dtFrom_Change() 
     ' Add 1 month to dttoDate To automatically
    Me.dtTo.Value = DateAdd("m", 1, Me.dtFrom.Value) 
End Sub 
 
Private Sub dtTo_Change() 
     'MsgBox DateDiff("d", Me.dtFrom.Value, Me.dtTo.Value)
    If DateDiff("d", Me.dtFrom.Value, Me.dtTo.Value) > 31 Then 
        MsgBox "You cannot select Date Range greater than 31 days!", vbCritical + vbOKOnly, "Warning" 
        Me.dtTo.Value = DateAdd("m", 1, Me.dtFrom.Value) 
    End If 
End Sub 
 
 'Loads the Combo Box with Sites from SQL and sets dtValues
Private Sub UserForm_Activate() 
     'set default date form to beginning of current month
    Me.dtFrom.Value = CDate("01/" & Month(Now) & "/" & Year(Now)) 
    Me.dtTo.Value = Now 
     
    Call LoadSites 
End Sub 
 
 '#####################################################
 ' My Subroutines/Functions
 '#####################################################
 
 'Finds  first empty row in ws "Exceptions"
Private Sub FindNextCell() 
    Dim iRow As Long 
    Dim ws As Worksheet 
    Dim NextCell As Range 
    Set ws = Worksheets("Exceptions") 
     'iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    If ws.Range("A1").Value = "" Then 
        ws.Range("A1").Select 
    Else 
        If ws.Range("A2").Value = "" Then 
            ws.Range("A2").Select 
        Else 
            ws.Range("A1").End(xlDown).Select 
            ActiveCell.Offset(1, 0).Select 
        End If 
    End If 
End Sub 
 
Function retYYYYMMDD(tmpDate As Date) As Long 
     ' converts date to YYYYMMDD format for easier paramater passing to queries
    Dim tmpChar As String, tmpRetVal As String 
     
    tmpRetVal = Year(tmpDate) 
    tmpChar = "0" & CStr(Month(tmpDate)) 
    tmpRetVal = tmpRetVal & Right(tmpChar, 2) 
    tmpChar = "0" & CStr(Day(tmpDate)) 
    tmpRetVal = tmpRetVal & Right(tmpChar, 2) 
    retYYYYMMDD = tmpRetVal 
End Function 
 
 
Private Sub LoadReceiptsModZero() 
    Dim qrySQL As String 
     '
    qrySQL = "SELECT vwSMTransactions.Site, TrxDate, UserLogin, AgreeNo, UnitsOccup, CustName, Description, DateBanked,
DateReconciled, TotAmt " 
    qrySQL = qrySQL & " FROM vwSMTransactions WHERE (vwSMTransactions.Charge=0) AND (vwSMTransactions.TotAmt=0) AND
(vwSMTransactions.CustName Is Not Null) " 
     
    If cmbSite.Text  "ALL" Then 
        qrySQL = qrySQL & " AND [Site] = '" & cmbSite.Text & "'" 
    End If 
     
    qrySQL = qrySQL & " AND ([YYYYMMDD] >= " & retYYYYMMDD(Me.dtFrom.Value) & ") " 
    qrySQL = qrySQL & " AND ([YYYYMMDD] = " & retYYYYMMDD(Me.dtFrom.Value) & ") " 
    qrySQL = qrySQL & " AND ([YYYYMMDD]

I have two issues that I need your assistance:

1) I am trying to use a cell for just the month/yr, the problem that I am having when data is entered, excel thinks I am putting in month/day and then gives current year.

How do I fix this?

(6/10 is turning into 6/10/08)

2) I also have a DOB cell, goal is to input numbers w/o slashes and have excel automatically input slashes into cell.
110150 is converting to 07/30/2201 instead of 11/01/1950.

Thanks!

I'm using VBA to convert a spreadsheet to HTML to email as the body. I found this great code and custom formula on www.rondebruin.nl.

Does anyone know how to reduce the size of the HTML? When printing the email, the right side does not show. Also, it prints on two pages.

If I could reduce it, this will work terrific.

Here is the code and custom formula.

Sub Email_Send()
****Dim oApp As Object
****Dim oMail As Object
****Dim oAddress1 As String
****Dim oAddress2 As String
****Dim oDate As String
****Dim oEndDate As Date
****Dim dest As Workbook
****Dim myshape As Shape
****oDate = Sheets("Checklist").Range("A1")
****oLoc = Sheets("Info").Range("F118")
****Application.DisplayAlerts = False
****oAddress1 = Sheets("Info").Range("F124")
****oAddress2 = Sheets("Info").Range("F125")
****oScore = Sheets("Checklist").Range("M65")
****Application.Goto Reference:="Print_Area"
****ActiveSheet.Copy
****Set dest = ActiveWorkbook
****For Each myshape In dest.Sheets(1).Shapes
********myshape.Delete
****Next
****Cells.ClearComments
****Cells.Copy
****Cells.PasteSpecial Paste:=xlPasteValues
****Range("E2,F65,L65").ClearContents
****Application.CutCopyMode = False
****ActiveSheet.PageSetup.Zoom = 60 '' Now() Then
********With oMail
********.To = oAddress1 ' Sends to full department until Mar 4
********.Subject = "" & oLoc & " " & Month(oDate) & "-" & Day(oDate) & "-" & Year(oDate) & " Warehouse Operational Checklist " & oScore
********.HTMLBody = RangetoHTML
********.Send
****End With
****Else
********With oMail
********.To = oAddress2 ' Sends to only department heads after Mar 4
********.Subject = "" & oLoc & " " & Month(oDate) & "-" & Day(oDate) & "-" & Year(oDate) & " Warehouse Operational Checklist " & oScore
********.HTMLBody = RangetoHTML
********.Send
****End With
****End If
****dest.Close False
****Set oMail = Nothing
****Set oApp = Nothing
End Sub

Function RangetoHTML()
****Dim fso As Object
****Dim ts As Object
****Dim TempFile As String
****TempFile = Environ$("temp") & "/" & _
****Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
****ActiveSheet.PageSetup.Zoom = 60
****With ActiveWorkbook.PublishObjects.Add( _
********SourceType:=xlSourceRange, _
********Filename:=TempFile, _
********Sheet:=ActiveSheet.Name, _
********Source:=Selection.Address, _
********HtmlType:=xlHtmlStatic)
********.Publish (True)
****End With
****
****Set fso = CreateObject("Scripting.FileSystemObject")
****Set ts = fso.GetFile(TempFile).OpenAsTextStream(1, -2)
****RangetoHTML = ts.ReadAll
****ts.Close
****Set ts = Nothing
****Set fso = Nothing
****Kill TempFile
End Function

Mike

I recieve date information in text format...i.e. "01-JAN-2005", but would
like to convert it to an Excel format date.

I have seen it done before by using a function to split the text into parts
(getting excel to read the cell as DD-MMM-YYYY) and output the result using
DAY, MONTH, YEAR function. No idea how to do this though.

Thanks
J

Hi all,

I have a class called clsTrade and in it:

+++
Dim m_dtSettleDate As Date

Property Let SettleDate(szSettleDate As String)
m_dtSettleDate = ParseStringToDate(szSettleDate)
End Property

Property Get SettleDate() As Date
SettleDate = m_dtSettleDate
End Property

Private Function ParseStringToDate(szDate As String) As Date
Const HYPHEN As String = "-"
Dim lYearPos As Long
Dim lMonthPos As Long

On Error GoTo ErrorHandler
With VBA
lYearPos = .InStr(1, szDate, HYPHEN, vbTextCompare)
lMonthPos = .InStr(lYearPos + 1, szDate, HYPHEN, vbTextCompare)

ParseStringToDate = .DateSerial(.Left$(szSettleDate, lYearPos - 1), _
.Mid$(szSettleDate, lYearPos + 1, lMonthPos
- lYearPos - 1), _
.Right$(szSettleDate, .Len(szSettleDate) -
lMonthPos))

End Function

ErrorHandler:
'Put today's day as default first
ParseStringToDate = .DateSerial(.Year(Now), .Month(Now), .Day(Now))
End With
End Function
+++

When compiled an error says by highlilghting the above Property Let,
"Definitions of property procedures for the same property are inconsistent,
or property procedure has an optional parameter, a ParamArray. Or an invalid
Set final parameter"

My intention:
1) Taking a string of Date (e.g., "2005-11-23")
2) Parse and convert it to Date format through ParseStringToDate function
and store it in m_dtSettleDate

Now, I am trying to set a return value of Property Let statement as Date and
cannot!

My convictions:
1) I think it is reasonbale to have Private Function ParseStringToDate in
this clsTrade
2) The Property Let takes 1 parameter of String, converts it to Date and
store it, which I think is again reasonable.

My questions:
1) Am I coding in a good OO principle (to some extent)?
2) How can I get around the error I am getting?

Thanks for your time.
---
Tetsuya Oguma, Singapore

In cell A1 I have the day, month, year, hour, minute, and second a particular
transaction took place. In cell A2 I have the day, month, year, hour, minute,
and second the next transaction takes place. The Start and End dates and
times are used to determine the total number of hours, minutes and seconds
for one transaction. I have the total number of hours, minutes, and seconds
for a given transaction in column A3. If the total time in column A3 is
greater than 12:00:00, I want to return the value of 1 Business Day in column
A4. If the total is less than 12:00:00 I want to return the total hours,
minutes, and seconds in decimal format in column A4. Is this scientifically
possible?

When importing a query dates are entered in excell as text (YYYYMMDD). Is
there a way to control how the query imports the data? If not, I am having
difficulty creating a formula that would tell me which birthdays occur 2-3
weeks from today.

My old formula worked using the text import and converting the text column
to the correct date format:
=IF(AND(DATE(YEAR(TODAY()),MONTH(P2),DAY(P2))>=TOD AY()+7,DATE(YEAR(TODAY()),MONTH(P2),DAY(P2))

I've got an Excel sheet for data-input. For dates it should be possible to define either a year or a complete date (day-month-year). If one defines a year it is being converted to 01-01-year. In latter case the exact date is not known or doesn't matter.

Anyway, for this reason I cannot use the DateFormat for the cells of a column ("E"), because it would turn 2005 into something like 12-03-1905 or something similar I can't use. I've formatted the cells as Text.

How to check if the entered full date is a valid date?

I can check with CInt(Mid(Cells(i, "E"), 4, 2)) > 12 in a For-loop with parameter 'i' per row and similar If-constructions if the individual day and month field are not faulty, but 31 April or 29 February (when there's only 28 days) shouldn't pass the check.

So I'm wondering how I can check the dates? IF it's possible.

Later on the Excel sheet's data will be imported by a PHP script where all fields are checked again just to be on the safe side and properly inserted into the database. Question "why bother with Excel-level checking?". Answer: it's been requested by my employer. If isolated day / month checking is as far as I can go... okay, nothing to be done about that, but IF there's a way to check if a complete date is valid.... please share your thoughts

Hello,

I have a historical data set with dates (shown in day/month/year format) that I would like to convert to automatically generated 'trading days'. Each year, there is a different number of trading days, i.e. in 1962 there are 246 trading days, the first being 5/1/1962, and the the last one being 31/12/1962. What I am struggling with is creating a formula or condition that identifies when a trading year starts over. That is, if I have a data set from 5/1/1962 until the 21/5/2009, I want to write a condition or formula that automatically resets the trading days on the last trading day of that particular year, whether it is 31/12/1962 or 28/12/1963. I want to avoid setting pre-determined trading days, as I would like to use this for various data sets, each having different trading days in different years. Does anyone have any idea of how I would go about doing this? Thanks for your time.

I recieve date information in text format...i.e. "01-JAN-2005", but would
like to convert it to an Excel format date.

I have seen it done before by using a function to split the text into parts
(getting excel to read the cell as DD-MMM-YYYY) and output the result using
DAY, MONTH, YEAR function. No idea how to do this though.

Thanks
J

Hi all,

I have a class called clsTrade and in it:

+++
Dim m_dtSettleDate As Date

Property Let SettleDate(szSettleDate As String)
m_dtSettleDate = ParseStringToDate(szSettleDate)
End Property

Property Get SettleDate() As Date
SettleDate = m_dtSettleDate
End Property

Private Function ParseStringToDate(szDate As String) As Date
Const HYPHEN As String = "-"
Dim lYearPos As Long
Dim lMonthPos As Long

On Error GoTo ErrorHandler
With VBA
lYearPos = .InStr(1, szDate, HYPHEN, vbTextCompare)
lMonthPos = .InStr(lYearPos + 1, szDate, HYPHEN, vbTextCompare)

ParseStringToDate = .DateSerial(.Left$(szSettleDate, lYearPos - 1), _
.Mid$(szSettleDate, lYearPos + 1, lMonthPos
- lYearPos - 1), _
.Right$(szSettleDate, .Len(szSettleDate) -
lMonthPos))

End Function

ErrorHandler:
'Put today's day as default first
ParseStringToDate = .DateSerial(.Year(Now), .Month(Now), .Day(Now))
End With
End Function
+++

When compiled an error says by highlilghting the above Property Let,
"Definitions of property procedures for the same property are inconsistent,
or property procedure has an optional parameter, a ParamArray. Or an invalid
Set final parameter"

My intention:
1) Taking a string of Date (e.g., "2005-11-23")
2) Parse and convert it to Date format through ParseStringToDate function
and store it in m_dtSettleDate

Now, I am trying to set a return value of Property Let statement as Date and
cannot!

My convictions:
1) I think it is reasonbale to have Private Function ParseStringToDate in
this clsTrade
2) The Property Let takes 1 parameter of String, converts it to Date and
store it, which I think is again reasonable.

My questions:
1) Am I coding in a good OO principle (to some extent)?
2) How can I get around the error I am getting?

Thanks for your time.
---
Tetsuya Oguma, Singapore