LAE

Welcome to the LAE community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Discussions

Members

Resources

Events

 View Only

  • 1.  Week Ending Date

    Employee
    Posted 09-26-2014 08:13

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: Leaner

    Hello All,

    I've got a time sheets report which is used weekly. These reports have a field with the date of the time sheet line.

    ie:
    ----Name-----|---Hours---|---- Date----
John Stewart | 8.5 hours | 2013-12-19
John Stewart | 9 hours   | 2013-12-24
    Here comes the question; how can I make a field which calculates the date of that week's ending day?

    ie:
    ----Name-----|---Hours---|---- Date----|-Week Ending Date
John Stewart | 8.5 hours | 2013-12-19  | 2013-12-21
John Stewart | 9 hours   | 2013-12-24  | 2013-12-29
    Thank you!


  • 2.  RE: Week Ending Date

    Employee
    Posted 09-26-2014 08:37

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ejones

    These types of date calculations are usually interesting and require using the mod() function. This seems to do the job.

    if firstExec then {
    # Pick a date that is on the day of the week you want
    baseDate = date(2014,6,7) # This is a Saturday
    }

    # calculate the number of days difference between the current date
    # and that day of the week
    dif = 'Date'.dateSubtract(baseDate).mod(7)

    # because somehow mod() often returns the negative valid answer and I want
    # the positive one
    if dif < 0 then dif = dif + 7

    # Calculate the number of days to move forward to the day of the week you want
    numDaysToMove = 7 - dif

    emit *
    #emit dif
    #emit numDaysToMove
    emit 'Date'.dateAdjust(numDaysToMove,"days") as "Week Ending Date"


  • 3.  RE: Week Ending Date

    Employee
    Posted 09-26-2014 09:17

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: Leaner

    Thanks for the quick reply!

    I really much liked how you figured that out, I did not think of that!

    I made some changes:
    - Changed the baseDate to a Sunday as we got guys working on Sundays too and it kept giving me Saturdays, of course.
    - Because we got guys working on Sundays, I added an if so that if it is a Sunday, it adjusts the days by '0'

    Whatcha think?


    if firstExec then {
# Pick a date that is on the day of the week you want
baseDate = date(2014,8,31) # This is a Sunday
}

# calculate the number of days difference between the current date
# and that day of the week
dif = TimeSheetDate.dateSubtract(baseDate).mod(7)

# because somehow mod() often returns the negative valid answer and I want
# the positive one
if dif < 0 then dif = dif + 7 

# Calculate the number of days to move forward to the day of the week you want
# If the date is on a Sunday, do not move
if dif == 0 then {
numDaysToMove = 0
} else {
numDaysToMove = 7 - dif
}

#emit *
emit TimeSheetDate
emit dif
emit numDaysToMove
emit TimeSheetDate.dateAdjust(numDaysToMove,"days") as "Week Ending Date"


  • 4.  RE: Week Ending Date

    Employee
    Posted 09-26-2014 09:24

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: ejones

    Sounds like that might work. There isn't a way to make one answer fit every situation so you'll almost always have to make adjustments for what you need.

    The only way to know if it is working right is to test it and make sure it is doing what you want. Make sure you test the end and beginning of the week and also days near the end and beginning.


  • 5.  RE: Week Ending Date

    Employee
    Posted 09-29-2014 11:55

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: rhallmark

    This is another way of achieving the desired result; use the weekday() function.

    The weekday() function returns the day of the week (0-6, 0 representing Sunday, 6 representing Saturday) for a given date.

    SomeDate = date(2014,9,28)
    WeekEndingDate = if SomeDate.weekday() == 0 then SomeDate else SomeDate.dateAdjust(7-SomeDate.weekday(),"days")