Javier,
Thanks. That pointed me in the right direction. I ended up with this as a computed temp table (only part of it shown here) `CalculatedYear`= (IYR4(OrderDate)) INTEGER, + `CalculatedMonth`= (IMON(OrderDate)) INTEGER, + `CalculatedWeek`= (IWOY(OrderDate)) INTEGER, + `CalculatedWeekStart`= ((DWE(OrderDate))-5) DATE, + `CalculatedWeekEnd`= ((DWE(OrderDate))-1) DATE, + `TrimmedWeek`= ((SGET((CTXT(CalculatedWeekStart)),5,1))+' - '+(SGET((CTXT(CalculatedWeekEnd)),5,1))) TEXT (20), + `CalculatedMonthName`= (TMON(OrderDate)) TEXT, + Jan From: <[email protected]> To: <[email protected]> Date: Wed, 24 Mar 2021 11:39:13 -0500 Subject: RE: [RBASE-L] - Date calculation Lots of ways to do this depending on your preference. The following function return the first weekend date for whatever DATE you enter: SET VAR vToday DATE = (DWE(.#DATE)) Now you have the next Saturday’s date, so you just need to subtract 5 days for the previous Monday and 1 for the previous Friday. set var vStart DATE = (ADDDAY(.vToday, -5)) set var vEnd DATE = (ADDDAY(.vToday, -1)) And you will have the beginning and end dates for the week and you can easily format them. You can probably combine the commands into one long command, but it would be more difficult to read in the future. You can also use the function IDWk which will tell which day of the week for any date. Then you can use a simple CASE construct to add or subtract days accordingly. I can think of other ways but the ones I mentioned are simple and easy to understand. Hopefully, this is what you were looking for. Javier, Javier Valencia, PE [email protected] O: 913-829-0888 C: 913-915-3137 From: [email protected] <[email protected]> On Behalf Of jan johansen Sent: Wednesday, March 24, 2021 6:59 AM To: [email protected] Subject: RE: [RBASE-L] - Date calculation Thanks all, Pointed me in the right direction. However, now I have another challenge that so far eludes me. So I know that using (IWOY(.#DATE)) today will 13. But I'm really trying to find out what the Monday and Friday of Week 13 are so I can display something like "Week of 3/21 - 3/26". Any suggestions appreciated. Jan From: Troy Sosamon <[email protected]> To: "[email protected]" <[email protected]> Date: Tue, 16 Mar 2021 15:33:04 +0000 Subject: RE: [RBASE-L] - Date calculation Jan, Historically I have found building a table full of business days works best, so that you can take into account holidays and special days off/snow days Along with a business day flag y/n Put one row in for each business day for the year using a simple loop and Idwk function and a Y flag and then go flip the flag on the holidays and any other special days when you don’t operate. Then you can just count the number of rows between any two dates where the flag = 'Y'. I actually had an app with 2 flags because the company was a car auction so actual business days for sellers and buyers were not always the same so there was a flag for each because sometimes it might not be a business day for the sellers if you cant go pickup cars because of a snowstorm, but are open for buyers who can make in to the location to pay their bills. This data was used for calculating buyer storage and seller pickup SLA reports. Just depends on your application and what you need to accomplish with it. Troy -----Original Message----- From: [email protected] <[email protected]> On Behalf Of A. Razzak Memon Sent: Tuesday, March 16, 2021 5:25 AM To: [email protected] Subject: Re: [RBASE-L] - Date calculation Jan, Here's how ... Use the IDWK function to exclude the 6th and 7th (Saturday and Sunday) day of the Week. -- IDWK returns the day of the week where Monday is 1. -- Example 01 SELECT * FROM tablename WHERE (IDWK(datecolumn)) NOT IN (6,7) -- Example 02 SELECT * FROM tablename WHERE (IDWK(datetimecolumn)) NOT IN (6,7) That's all there is to it! Have fun. Very Best R:egards, Razzak At 07:15 AM 3/16/2021, jan johansen wrote: >Group, > >Anyone got a good date algorithm that ignores weekends? Or in other >words, only counts workdays? > >Jan -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/0Lm2Pn-1lvSWt3yqH-00Zixe%40mrelay.perfora.net. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/CH2PR06MB67435C0EED5561DE3F4BB1FD9C6B9%40CH2PR06MB6743.namprd06.prod.outlook.com. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/WC20210324115914.830183%40jjcalibrations.com. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/!%26!AAAAAAAAAAAYAAAAAAAAABa4qWuusAlBoskXxE2AV1mChgAAEAAAAJXbeCWuGVZCkfk8wxrrMxMBAAAAAA%3D%3D%40vtgonline.com. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/WC20210324175526.13000D%40jjcalibrations.com.
