For all the Date Guru's out there

[Joe Byrne]

I need to calculate the same 'day' of the previous year. For example, if the main date is Monday, Dec. 3, 07, I need to find the date of Monday Dec ??, 2006.

There are a couple of ways to get here, but most of what I'm coming up with are "brute force" methods. Wondering if anyone has tackled this before.

[Mel Bishop]

Search the forum for "astroday". Should work for you.

[Joe Byrne]

Thanks Mel.

Unless I'm missing something, the 'astroday' is only doing what I already have. I know how to add/subtract days, etc. but I need to find the calendar date that is the same as a selected date.

For example, If you take Monday 12/3/07 and just subtract 365 days (one year) you end up at Sunday 12/3/06. I need to end up on Monday 12/4/06.

What's happening here, is I'm writing a sales analysis report and I need to compare sales for the same day-of-week one year prior. This may be 365 days different, 364 days, etc.

Is there something with the Astro day function that does this that I'm missing?

[Peter Jinks]

Hi Joe,

I may be missing something (it's very late here), but surely if you subtract 365 days you guarantee that you won't have the same day, since 365 does not divide by 7 exactly.

Subtract 364 and you'll have the same day, every time. If your start date is the last of the month, you'll need to increase that by 7 - so subtract 371 to keep it in the same month as your start date (e.g. last Monday in December or whatever).

But in any event, you surely only need to check a maximum of 3 days - your start date minus 357, 364 and 371 days - each of which will be the same day of the week as your starting date. One of them is sure to be in the same month, isn't it?



Anyway, I'm off to bed!

Regards,

Pete.

[Peter Lameijn]

This gives you the same day in a previous or future year. If it doesn't exist, function returns 0, else -1
For instance if you enter dec 31, 2007, that is the fifth monday of dec. Last year only had 4 mondays in dec, so error is given...
Example uses current date.

#Compile Exe
#Dim All
#Include "Win32Api.inc"
 
Function GetPrevDay(ST_Old As SYSTEMTIME, ByVal Years As Long) As Long
  Dim LST As SYSTEMTIME, FT As FILETIME, DayDiff As Long
  LST = ST_Old
  LST.wDay = 1
  If SystemTimeToFileTime (LST, FT) Then
    If FileTimeToSystemTime (FT, LST) Then
      DayDiff = LST.wDayOfWeek
      LST.wYear = LST.wYear + Years
      If SystemTimeToFileTime (LST, FT) Then
        If FileTimeToSystemTime (FT, LST) Then
          DayDiff = DayDiff - LST.wDayOfWeek
          LST = ST_Old
          LST.wDay = LST.wDay + DayDiff
          LST.wYear = LST.wYear + Years
          If SystemTimeToFileTime (LST, FT) Then
            If FileTimeToSystemTime (FT, LST) Then
              ST_Old = LST
              Function = -1
              Exit Function
            End If
          End If
        End If
      End If
    End If
  End If
End Function
 
Function PBMain () As Long
  Dim ST As SYSTEMTIME
  GetLocalTime ST
  If GetPrevDay (ST, -1) Then
    MsgBox "Day is " & Choose$(ST.wDayOfWeek+1, "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday") & ", " & _
                       Format$(ST.wMonth) & "/" & Format$(ST.wDay) & "/" & Format$(ST.wYear)
  Else
    MsgBox "Date requested doesn't exist"
  End If
End Function

[Egbert Zijlema]

Three possibilities

To be honest, it is not clear to me (maybe my age) what exactly Joe is up to. There are three possibilities, actually:

1. Same date a year ago, i.e. 12/04/2007 = 12/04/2006
2. Date exactly a year ago.
3. Whateverday of the month a year ago, for instance: first Tuesday of December

Possibility number 1 is simple of course.
To calculate a day exactly a year ago (#2) you have to convert the date to an integer value, then substract 365 or 366 (leap year involved) and finally reconvert the new value to a valid Windows date format.
For the third possibility Peter Lameijn offers a nice solution.

There are several methods to convert a date to an integer value and back. One possibility is making use of my Gregorian Date Library. It includes several routines for date manipulation, for instance how to find such dates as "the first Tuesday of December". You may want to download it here: http://zijlema.basicguru.eu/files/gregorian.zip

[Peter Lameijn]

Egbert,

I think Joe wants the date of the equivalent day in another year. For instance if today would be the third tuesday in December 2007, he wants the date of the third tuesday in December, in another year ...

[Michael Mattias]

FWIW....

I think the idea of 'sales on the same day of the same week' based on the calendar date is a useless sales figure. Pegging the day to a known 'event' might make some sense.

If this is retail, then perhaps the "Monday (Tues, Wed, etc) following Thanksgiving" might make some sense, as might the "third Thursday before Christmas" or "twelve days before Labor Day" but in general I think a date-based comparison like this is just not apples for apples.

In the non-retail world (my world), any such comparisons would be a total waste of time.

Maybe this is why many consumer-products companies define 52 'weeks' and do sales analysis by "week number." (???)

MCM

[Thomas Tierney]

Joe,

Not sure if this will work, in theory it should. convert the date to a julian date for the current year, take the same julian date and convert it to the date for the previous year?

My 2 cents

[Egbert Zijlema]

I think this is what Joe means

What Joe means is easy to do (using the Gregorian Library). This is how to accomplish it:

• Get day of the week for original date
• Calculate an integer value (Gregorian) for same date in previous year (year minus 1, same month, same day)
• LOOP, while adding 1 until the new value returns same day of the week

Try this code (don't forget to download my Gregorian functions first , otherwise this won't work).

#COMPILE EXE
#INCLUDE "GREGORIAN.INC"

%LOCALE_USA = 1033

FUNCTION SameDateLastYear(BYVAL wYear AS WORD, BYVAL wMonth AS WORD, BYVAL wDay AS WORD) AS LONG
  LOCAL tmpGreg AS LONG, wWeekDay AS WORD
  
  wWeekDay = DayOfWeek(wYear, wMonth, wDay)                   ' original day of week
  tmpGreg = Gregorian(wYear - 1, wMonth, wDay)                ' gregorian for same date prev. year
  DECR tmpGreg                                                ' set zero point for counter
  DO
    INCR tmpGreg
  LOOP UNTIL GregDayOfWeek(tmpGreg) = wWeekDay                ' loop until same weekday
  FUNCTION = tmpGreg                                          ' return Gregorian Day Number
END FUNCTION


FUNCTION PBMAIN () AS LONG
  LOCAL x AS LONG, st AS SYSTEMTIME
  LOCAL sToDay AS STRING, sYearAgo AS STRING

  GetLocalTime st
  sToDay = GregDateFormat(%DATE_LONGDATE, st, %LOCALE_USA)
  x = SameDateLastYear(st.wYear, st.wMonth, st.wDay)
  GregorianToSysDate x, st
  sYearAgo = GregDateFormat(%DATE_LONGDATE, st, %LOCALE_USA)
  
  MSGBOX "Today: " & sToDay & $CRLF & _
         "Last year: " & sYearAgo, 64, "Same day last year"

END FUNCTION

[Paul Purvis]

for what is worth,
you might try to build two strings, from the two months that then do a search for certain strings. Ok ok ok, a picture is worth a thousand words.

In making the posting on this forum my extra spaces where wiped out between the day of the week and number of the month.

Strings have a year and a month then days of the week, all days of the week have 6 characters followed by two characters that represent the number day in the month.

I would believe you have to add onto the end of the below strings extra days
like "TTTTT 31 WWWWW 31 HHHHH 31 FFFFF 31" on the dates string 200712 and
"MMMMM 31 TTTTT 31 WWWWW 31 HHHHH 31 FFFFF 31" on the 200612 string.

S=Saturday
U=Sunday
M=Monday
T=Tuesday
W=Wednesday
H=Thursday
F=Friday


"200712 S 01 U 02 M 03 T 04 W 05 H 06 F 07 SS 08 UU 09 MM 10 TT 11 WW 12 HH 13 FF 14 SSS 15 UUU 16 MMM 17 TTT 18 WWW 19 HHH 20 FFF 21 SSSS 22 UUUU 23 MMMM 24 TTTT 25 WWWW 26 HHHH 27 FFFF 28 SSSSS 29 UUUUU 30 MMMMM 31"

"200612 F 01 S 02 U 03 M 04 T 05 W 06 H 07 FF 08 SS 09 UU 10 MM 11 TT 12 WW 13 HH 14 FFF 15 SSS 16 UUU 17 MMM 18 TTT 19 WWW 20 HHH 21 FFFF 22 SSSS 23 UUUU 24 MMMM 25 TTTT 26 WWWW 27 HHHH 28 FFFFF 29 SSSSS 30 UUUUU 31"


the above can be an array as well
do a seach in the first string for the 03 then read the left 6 characters into a variable that has been trimmed, lets say A$.
then do a search in the second string for the variable with spaces added on the front and back A$=SPACE+A$+SPACE$, then read the number to right of the variable found.

[Joe Byrne]

Got stuck trying to get back to Mn from Florida for the last 48 hrs, sorry for the delayed "thanks!" to everyone.

In the non-retail world (my world), any such comparisons would be a total waste of time.

Michael, Its a BIG world out here, and I'm willing to bet that 99.9% of the people who are looking for a software solution to a real-world problem actually have a real world problem. In my case, this has nothing to do with retail or non-retail. It has to do with reservations and a Day vs Same Day comparison not only make sense, but its crucial to deciding what resources to have available.

[Egbert Zijlema]

New functionality in Gregorian library

Thank you very much, folks!
This very thread inspired me to add some functionality to my calendrical masterpiece :saythat:, the Gregorian Date Library. All the files of the newest version, both library and demo's, are timestamped 07:12, which means it is the December-version of 2007. It's free, so fetch it!

[Michael Mattias]

I'm not saying that you don't need what you need... but I am saying the way the problem is posited there is no one correct answer, meaning whatever you get is indeterminate, meaning whatever you get cannot be useful.

Let's take your test case:

For example, if the main date is Monday, Dec. 3, 07, I need to find the date of Monday Dec ??, 2006.

Which Monday in Dec 2006 do you want? I can think of four* 'correct' answers:

• The Monday CLOSEST TO in 'day of month' (in this case the third) in Dec 2006? (fortunately, because the number of days in a week is odd there will never be a tie).
• The first Monday preceding Dec 3 2006
• The first Monday following Dec 3 2006
• The "Nth" Monday of December 2006 where N is the ordinal Monday of Dec 3 2007

All four correct answers are easily calcuable; but only one can be the one you really want.

* two answers can be (will always?) be the same.

[Joe Byrne]

Well Michael, thanks for the input, but at least the others got it. It's really not all that complicated.

[Paul Purvis]

i understand what michael means.

we are in the financial world and there are may variables that come to play in comparing dates.

the only solution for us would be to compare dates using all comparitive dates then let the human decipher what is needed.

possibly all dates comparisons are really needed in management.
even dates where you are comparing a difference between future and past holidays.

It really all depends on what figure one is looking for.
That is what makes some people more insightful that others in managing.

[Michael Mattias]

You're right, I don't get it.

Continuing your example..

For example, If you take Monday 12/3/07 and just subtract 365 days (one year) you end up at Sunday 12/3/06. I need to end up on Monday 12/4/06.

But why did you select 12/4/06?

Is it because 12/4/06 is......

A) The closest Monday by date-of-month (4th vs 3rd, difference one)?
B) The same ordinal Monday (first) of the month?
C) The first Monday following Dec 3 2006?

In this case the same date satisfies three of the four possible 'correct' answers, so it's pretty easy to be "right."

However, any program code to be written needs to know which rule to follow.

his is the kind of thing I have seen more times than I can count: ambiguous specification from the user followed by the 'incorrect correct' answer (never thought you'd see those two words used together like that, did you?) because the programmer used a different - but completely valid - rule.

Of course in a case like this I DO blame the programmer, because he made two (2) mistakes:
1 - Accepting the task without clearing up the instrinsic ambiguity
2 - "Assuming" what the user meant and programming to that assumption.

Oh well, at least I have another entry for my file on "why communications skills are important for programmer/analysts."

MCM

[Joe Byrne]

Well, I think this is the first time in my life I've been accused of poor communication skills. In any event, I know precisely what the customer is asking for and I even know a couple of ways to get there, thanks in part of helpful replies here. For this industry, it also makes perfect sense.

But obviously, my mistake was not stating the need at a high enough level to be acceptable. Nonetheless, my continued thanks to those who posted useful ideas.

Before this gets any further into a discussion best suited for the café, I shall end my part of it now.

[Egbert Zijlema]

Compare it to your birthday, MM!

For example, If you take Monday 12/3/07 and just subtract 365 days (one year) you end up at Sunday 12/3/06. I need to end up on Monday 12/4/06.

Michael Mattias is asking why, in this example, Joe wants to end up on that specific Monday. Well Michael, it's very simple. Compare it to your birthday. When it falls on a Tuesday this year, it fell on either a Monday or a Sunday last year, depending whether a leap year is involved or not. But, as a matter of speaking, Joe tries to let his birthday fall on the same day every year.

Because it's always tricky to substract 364 days (sometimes you need 365) it's more safe to follow the steps I pointed out earlier in this thread:

• check weekday
• find an integer value for Sunday 12/03/06 (following the above example)
• add 1 or 2 days using a DO ... LOOP that checks for the same weekday

You may want to use my Gregorian Library for it, but it is not necessarily the only solution. There are several methods to convert calendar dates to integers and back.

[Michael Mattias]

>add 1 or 2 days using a DO ... LOOP that checks for the same weekday

You are assuming he wants the Monday following the same date one year ago.

Sans that assumption, why not subtract one or two days using a DO..LOOP that checks for the same weekday to get the first Monday PRIOR TO the same date one year ago?

I stand by my statement: The orginal question has no one correct answer since insufficient information was supplied vis-a-vis the rules to be used.