Date Function in a Macro 1

[JeaShe]

I'm writing a macro where I want to perform several date functions. Essentially I use a GetString to pick up a date from the host, e.g., "01/15/04"

1. I'd like to determine if the mm/dd part of the date is prior to "12/31" and if so, change it to the last day of the month for said "mm" and add one full year to it as well, e.g., "01/15/04" becomes "01/31/05"
2. If, a choice is made to use calendar year vs actual "mm/dd," I'd like it to become the calendar year without adding another year, e.g., 01/15/04 becomes 12/31/04

4. Then once the initial date scheme is settled (1-2 above) I need to increase the date by one year for each iteration I'm running. From that date I want to be able to add 1 year to it. Not just 365 days as it gets confusing in case of leap year.

I do have many snippets of code I've tried to accomplish this with but fear major embarassment if I supply it. I will add one trial piece for perusal:

tmp = objWorkBook.Worksheets("ListInfo").cells( xlRow, 5 ) 'formatted as mentioned above
IncDt = Format( tmp, "mm/dd/yy") 'attempt to format
tmp = "" 'clear to reuse variable

tmp = objWorkBook.Worksheets("ListInfo").cells( xlRow, 4 ) 'captures mm/dd only
minDate = Format( tmp, "mm/dd") 'just to pull those values out to run function to either use calendar year ending or last day of listed month
tmp = ""

yrShort = Format (IncDt , "yy") 'to get yy only for a function (can't actually remember what)

yrLong = Format (IncDt , "yyyy") 'this to get a full ccyy as when comparing years from 1900's to 2000's must have full ccyy.


Thank you for your assistance.

Jeane

 

[calculus]

Without going into too much detail I thought I might give you some functions that will help you out. As you are discovering, working with dates is a bit tricky.

First, it looks like you're working in Excel more than in Extra. Is this code for Extra!Basic or VBA? If you're trying to do this in EB but need output in Excel, I'd suggest you copy the code and paste it into Excel. Excel will give you a lot of date function (listed below) that Extra doesn't.

Year(Date$)
Month(Date$)
Day(Date$)
WeekDay(Date$) - Gives you the day of the week

So, from above:
1. MyDate = CVDate(Month(MyDate) + 1 & "/1/" & Year (MyDate) + 1) - 1
I've made this date "2/1/2005", then subtracted a day. You'll have to work out the end of year problem.

2. I'm not sure what you're needing here

3. Missing from above

4. MyDate = Year(MyDate) + 1

If you take this a step further, you can add a user form with a date/time picker that will make this a lot easier as well.

calculus

 

[JeaShe]

Thank you for your suggestions. I'm using Excel as a place to deposit data from the host until I need it again. Plus the workbook is accessed from a server so it can be finicky with losing formatting (Microsoft even admits this~).

I don't see how you made your date 2/1/5... Do you mean you assigned those values to the variables you declared?

Could you please provide it in Extra Basic for me?

Thanks

 

[calculus]

Sorry, Extra Basic simply doesn't work that way. You'll have to develop all your own functions. You'll have to split the Date out to Month, Day and year, then use logic to do the things you want. You'll also have to hard code what months have 31 days in them, then handle leap year. To put it simply, EB doesn't have adequate Date handling functions built in.

You are better off in this using VBA. Your code will likely work if you simply paste it into a new module in VBA. If the formating is an issue, a function that refreshes formating may be the answer?

The other option is to create Excel objects in EB. This is a bit tricky as you have to know exactly what the functions your calling are - there is not a helpful GUI. I've done this in the past, but the better option for me has always been to move the code to VBA.

Also, you don't have to have the code in the same workbook as the data. You can create a "code" workbook this way.

Good luck,

calculus

 

[JeaShe]

Calculus:
what do you mean by the following (or how do I go about it):

"Also, you don't have to have the code in the same workbook as the data. You can create a "code" workbook this way."

 

[calculus]

The code is stored in the workbook, but as long as the workbook is open, all other books have access to the same functions and subs.

You can also create XLA files to be used as Add-ins. I've discovered this since my last post and it works great. Search on Add-Ins on Google for more info.

calculus

 

[GComyn]

For Excel VBA, since there wasn't a DateAdd function, (which I use all the time in Access), I created it. here is the code:

Public Function DateAdd(strInterval As String, _
         intNumber As Integer, datDate As Date) As Date
  Dim intMonth As Integer
  Dim intDay As Integer
  Dim intYear As Integer
  Dim intTemp As Integer
  Dim strDate As String
    
  intMonth = CInt(Format(datDate, "MM"))
  intDay = CInt(Format(datDate, "DD"))
  intYear = CInt(Format(datDate, "YYYY"))
  
  Select Case UCase(strInterval)
    Case "D"
      DateAdd = datDate + intNumber
    Case "M"
      intMonth = intMonth + intNumber
      If intMonth > 12 Then
        intTemp = intMonth / 12
        Do Until intMonth <= 12
          intMonth = intMonth - 12
        Loop
        intYear = intYear + intTemp
        strDate = CStr(intMonth) & "/" & CStr(intDay) & "/" & CStr(intYear)
        DateAdd = CDate(strDate)
      ElseIf intMonth = 0 Then
        intYear = intYear - 1
        intMonth = 12
        strDate = CStr(intMonth) & "/" & CStr(intDay) & "/" & CStr(intYear)
        DateAdd = CDate(strDate)
      Else
        strDate = CStr(intMonth) & "/" & CStr(intDay) & "/" & CStr(intYear)
        DateAdd = CDate(strDate)
      End If
    Case "Y"
      intYear = intYear + intNumber
      strDate = CStr(intMonth) & "/" & CStr(intDay) & "/" & CStr(intYear)
      DateAdd = CDate(strDate)
  End Select
End Function

It _seems_ to work all of the time.... I did some extensive testing on it, but I may have missed something.

JeaShe, you said something about wanting to add a year... you can do that by the following line of code:

MyDate = dateadd("y",1,MyDate)

I know that this isn't about Extra, but it is related to the question asked.....

 

[JeaShe]

I'm definitely going to try this out. Thanks so much!

 

[calculus]

You might also try the included functionality.

To get 10 days from now:
NewDate = Date + 10

Office will handle all the details. I think you can also subtract dates to find out how many days are between them, but I haven't used this myself.

Office does a wonderful job with dates, but you do have to be sure the variable is declared as a date or changed to a date via cvDate(string).

calculus