Please help am having date issues.

[iSPOK]

Please help.......... I have a what seems an improbable tast, and am not a natural programer and I will be very greatfull if any one with the know how could help..
I am attempting to write a piece of code that can look at any date and do the following:

I have 4 reporting periods 01/mm/yyyy, 08/mm/yyy, 15/mm/yyyy/ and 22/mm/yyy.... in sort (1.8.15and 22nd of every month)....

Is there a way to code a script that could, convert a given date from a screen capture to one of the following dates..

e.g. if my scripts caputers any given date say 11/02/07.
Is there a way of writing a piece of code, that can take the prsented date snd set it back to one of the reporting periods above,,,

...( so if I get a date of 11/02/07, the code would set a default date of, 01/02/07).. However if I get a date of 08/02/07, then it would only set the date back to 01/02/07.......

In sort any given date would be set back to two of the reporting periods above...

Sorry in advance, I do not have a piece of code to start with as I don't know where to begin....

P.S... I am not trying to capture system date.... Just a field on the screen which has a date field,, its static, in a position ( 14, 07) on the screen....

I would apreciate anyons help......

 

[SkipVought]

Hi,

Your resuirement is not clear.

You have the following DAYS reporting periode: 1, 8, 15 & 22.

Any date from your screen is to be "...set back to two of the reporting periods." but not less than the first of that daty's month?

Examples:
30 - set to 15
22 - set to 8
15 - set to 1

Hmmmmmm. Is ther any day that gets set to 22?

Or did I totally misunderstand what you wanted to say?

Skip,

_[red][/red]

 

[iSPOK]

Hi skip...

Thanks for taking a look at this.... Its a bit of a mind boggle....
Here is what am attempting to do, for the 4 reporting periods,, 01, 08, 15 and 22nd...

Its goes somthing like this..say I pick a date of 02/02/07..then I would need to go back 2 reporting periods, so I should get a reporting period of 22nd..

if I get a date of 30/02/07 then I would need to go back to the 15th...

however if I get a date of 01/02/07, because the 01st is a reporting period, I would count that as one, then the second would be 22/01/07,..

The idea is to count back two reporting periods, except for when the date falls on a reporting period, then we count that as the first period...

thanks for taking an intrest in this....

 

[Skie]

Any day 1-7 goes to 15 of the previous month.
Any day 8-14 goes to 22 of the previous month.
Any day 15-21 goes to 1 of the current month.
Any day 22-31 goes to 8 of the current month.

'This variable would hold your scraped data
dtmScrape = datevalue("01/01/2006")
If day(dtmScrape) < 8 Then
    sDay = "15"
    sMonth = cStr(cInt(Mid(dtmScrape,4,2))-1)
ElseIf day(dtmScrape) < 15 Then
    sDay = "22"
    sMonth = cStr(cInt(Mid(dtmScrape,4,2))-1)
ElseIf day(dtmScarpe) < 22 Then
    sDay = "01"
    sMonth = Mid(dtmScrape,4,2)
Else
    sDay = "08"
    sMonth = Mid(dtmScrape,4,2)
End If
dtmScrape = sDay & "/" & sMonth & "/" right(dtmScrape,4)

I'm not sure if it'd default to a dd/mm/yyyy format, so you might need to put it in as mm/dd/yyyy format and then use the format date function to change it.

 

[iSPOK]

Hi Skie
Thanks for your responce.... I will try insertimg that code into the VB edito in excel....

just to clarify how the dates work...
Any day 2-7 goes to 15 of the previous month.
Any day 9-14 goes to 01 of the previous month.
Any day 16-21 goes to 08 of the current month.
Any day 23-31 goes to 8 of the current month.

For dates of reporting periods:
01 goes to 22 of the previous month.
08 goes to 01 of the current month.
15 goes to 08 of the current month.
22 goes to 15 of the current month.

We count backwards, but the reporting period is the first count as you can see above...

 

[iSPOK]

Skie

Please help,,,
You code works, with only one small issue.
Any date the I capture, will only default to the 15th /mm/yyyy,
is there a way to make it work for the rest of the conditions, in the code...??

 

[Skie]

Sub Main
  dtmScrape = datevalue("01/22/2007")
  dtmScrape = Format(dtmScrape,"mm/dd/yyyy")
  If day(dtmScrape) = 1 Then
      sDay = "22"
      sMonth = cStr(cInt(Left(dtmScrape,2))-1)
  ElseIf day(dtmScrape) < 8 Then
      MsgBox "<8"
      sDay = "15"
      sMonth = cStr(cInt(Left(dtmScrape,2))-1)
  ElseIf day(dtmScrape) < 15 Then
      MsgBox "<15"
      sDay = "01"
      sMonth = Left(dtmScrape,2)
  ElseIf day(dtmScrape) < 22 Then
      MsgBox "<22"
      sDay = "08"
      sMonth = Left(dtmScrape,2)
  Else
      MsgBox "Else"
      sDay = "15"
      sMonth = Left(dtmScrape,2)
  End If
  If sMonth = "0" Then sMonth = "12"
  dtmScrape = dateValue(sMonth & "/" & sDay & "/" & right(dtmScrape,4))
  dtmScrape = Format(dtmScrape,"mm/dd/yyyy")
  MsgBox dtmScrape
  dtmScrape = Format(dtmScrape,"dd/mm/yyyy")
  MsgBox dtmScrape
End Sub