Announcement

Collapse
No announcement yet.

For all the Date Guru's out there

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • For all the Date Guru's out there

    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.
    Software makes Hardware Happen

  • #2
    Search the forum for "astroday". Should work for you.
    There are no atheists in a fox hole or the morning of a math test.
    If my flag offends you, I'll help you pack.

    Comment


    • #3
      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?
      Last edited by Joe Byrne; 3 Dec 2007, 10:11 PM.
      Software makes Hardware Happen

      Comment


      • #4
        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?

        :confused2:

        Anyway, I'm off to bed! :yawn2:

        Regards,

        Pete.
        Last edited by Peter Jinks; 3 Dec 2007, 10:27 PM. Reason: Being tired...

        Comment


        • #5
          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.

          Code:
          #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
          Last edited by Peter Lameijn; 4 Dec 2007, 04:32 AM.
          Regards,
          Peter

          Comment


          • #6
            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
            Last edited by Egbert Zijlema; 4 Dec 2007, 06:26 AM.

            Egbert Zijlema, journalist and programmer (zijlema at basicguru dot eu)
            http://zijlema.basicguru.eu
            *** Opinions expressed here are not necessarily untrue ***

            Comment


            • #7
              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 ...
              Regards,
              Peter

              Comment


              • #8
                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
                Michael Mattias
                Tal Systems Inc. (retired)
                Racine WI USA
                [email protected]
                http://www.talsystems.com

                Comment


                • #9
                  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
                  Sr. Software Development Engineer and Sr. Information Security Analyst,
                  CEH, Digital Forensic Examiner

                  Comment


                  • #10
                    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).
                    Code:
                    #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
                    Last edited by Egbert Zijlema; 4 Dec 2007, 02:12 PM.

                    Egbert Zijlema, journalist and programmer (zijlema at basicguru dot eu)
                    http://zijlema.basicguru.eu
                    *** Opinions expressed here are not necessarily untrue ***

                    Comment


                    • #11
                      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.
                      Last edited by Paul Purvis; 4 Dec 2007, 08:48 PM.
                      p purvis

                      Comment


                      • #12
                        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.
                        Software makes Hardware Happen

                        Comment


                        • #13
                          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!

                          Egbert Zijlema, journalist and programmer (zijlema at basicguru dot eu)
                          http://zijlema.basicguru.eu
                          *** Opinions expressed here are not necessarily untrue ***

                          Comment


                          • #14
                            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.
                            Last edited by Michael Mattias; 5 Dec 2007, 02:12 PM.
                            Michael Mattias
                            Tal Systems Inc. (retired)
                            Racine WI USA
                            [email protected]
                            http://www.talsystems.com

                            Comment


                            • #15
                              Well Michael, thanks for the input, but at least the others got it. It's really not all that complicated.
                              Software makes Hardware Happen

                              Comment


                              • #16
                                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.
                                Last edited by Paul Purvis; 5 Dec 2007, 03:45 PM.
                                p purvis

                                Comment


                                • #17
                                  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
                                  Michael Mattias
                                  Tal Systems Inc. (retired)
                                  Racine WI USA
                                  [email protected]
                                  http://www.talsystems.com

                                  Comment


                                  • #18
                                    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.
                                    Software makes Hardware Happen

                                    Comment


                                    • #19
                                      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.

                                      Egbert Zijlema, journalist and programmer (zijlema at basicguru dot eu)
                                      http://zijlema.basicguru.eu
                                      *** Opinions expressed here are not necessarily untrue ***

                                      Comment


                                      • #20
                                        >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.
                                        Michael Mattias
                                        Tal Systems Inc. (retired)
                                        Racine WI USA
                                        [email protected]
                                        http://www.talsystems.com

                                        Comment

                                        Working...
                                        X