1. Date Query

Hello

I am trying to convert a week number and a year into a date.

For example:

Monday September 2nd 2019 = Week Number 38.

Thanks you for your help. It is much appreciated.

Kind regards

Alex

2. Re: Date Query

Hi Alex have a look at WEEK() ,WEEK_ISO() and WEEK_ISO_FULL() Functions in the documentation. They should point you in the right direction.

3. Re: Date Query

Hello Lance

Sorry, I made a mistake in explaining the query.

The example should be
38 2019 = I need to see 02/09/2019

Thanks you for your help. It is much appreciated.

Kind regards

Alex

4. Re: Date Query

Oops wrong function..
nth_dow()

5. Re: Date Query

Misunderstood the question sorry

6. Re: Date Query

Not if you only have the year and week number, Al. ( Unless of course I am talking rubbish.)

For the sake of simplicity, I'd create a table with the week numbers and dates - either the start or end of the week as you wish - and do a lookup based on the week number.

7. Re: Date Query

Originally Posted by Ted Giles
Not if you only have the year and week number, Al. ( Unless of course I am talking rubbish.)

For the sake of simplicity, I'd create a table with the week numbers and dates - either the start or end of the week as you wish - and do a lookup based on the week number.
Does bring up the issue of what date of the week is one looking for? First day through 7th day...

A date is only one day. A week is seven days.

8. Re: Date Query

Yep, but week numbers are more of a universally agreed metric.
If you lookup the process in Excel, you can set a range of 7 days to define a week.
So, Mon - Sun is one week no., Sun -Sat is another.
I think you can change the start and end days of the week.

Interesting challenge though.

9. Re: Date Query

01/01/2019 + Week# times 7

10. Re: Date Query

Originally Posted by Ted Giles
01/01/2019 + Week# times 7
Doesn't that give the date of the start of the next week?

11. Re: Date Query

Originally Posted by alexmuir
The example should be
38 2019 = I need to see 02/09/2019

Alex
This link says that week 38 of 2019 starts on 16 Sept 2019

http://week-number.net/calendar-with...bers-2019.html

This also begs the question of what day of the week 38 do you want? I see implied day 1 - ie Monday.

12. Re: Date Query

I think it need to be:

(FristDayOfTheYear + (Week# * 7)) - (7 + dow(FristDayOfTheYear)-1)) = FirstDayOfWeek#

13. Re: Date Query

Code:
`yearstart_iso(2019)+(38*7)`

14. Re: Date Query

Ted

We have decided to pursue you advice of creating a table.

Thanks to everyone who offered suggestions.

Kind regards

Alex

15. Re: Date Query

You are welcome.
It's probably easiest but the down side is that you will need to recreate one for next year.
Still, with the process done once, it should be relatively easy to replicate.

16. Re: Date Query

Is this query used to find a payroll pay date?

17. Re: Date Query

Again, why not use
Code:
```teststr="38 2019"
?yearstart_iso(val(word(teststr,2)))+((val(word(teststr,1))*7)-1)```
Single line of code, no tables, no maintenance required.

18. Re: Date Query

Originally Posted by MoGrace
Is this query used to find a payroll pay date?
No its not. But there is a tale!

We are implementing our PRECISE360 application into a supplier to a multi international who supplies their product orders via schedules.

Initially it was hard copy.
Despite a lot of effort using scanning software, we couldn't get it to provide reliable results.

After months and scores of calls we eventually got them to supply the schedules in a csv format.
These schedules list weekly requirements for products starting each Monday.

We import the schedules into PRECISE360 which eliminates 3+ hours of highly error prone admin time and automatically creates works orders for our client.
Another boring, tedious job eliminated thanks to the capability of Alpha!

Kind regards

Alex

19. Re: Date Query

@ Jon, that's a cool piece of logic!
Changing the -1 to -7 gives the Monday for week 38.
A challenge might be - with a Multi-National - to keep the dates in some sort of order or compliance across regional boundaries.

Anyway, nice job.

20. Re: Date Query

teststr="38 2019"
vdate=yearstart_iso(val(word(teststr,2)))+((val(word(teststr,1))*7)-7)
?ctod(ui_get_date_calendar("",dtoc(vdate)))
= {09/16/2019}

pretty cool

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•