Posts by m4Owen

    Hello


    This is a bit strange, but forms arent my usual forte. I have spent days on this issue so hoping someone can help.


    If my code starts a fresh form, it will then execute the layout event. However, if the code determines data for a date, it produces the userform correctly with previous data populated but the layout event does NOT fire (which means my class wont kick in which looks for changes in a text box).


    Does anyone have any tips as to what it can be? The odd thing is the way it builds the userform is similar - it is just from either a data sheet if there is previous, or from inventory if "new" (it is a stockcount to be used on a touchscreen pc) and the counts set to 0.


    I am quite pleased to have got this far with it, but I am out of ideas as to why the layout wont consistently trigger after the form is loaded.

    Hello Carim


    Thank you for your time helping me - its got my head in the right space for it to work. I need some time to come back to this - the workbook has 10000 lines of code in and I have to get this finished before I can test and im finding issues with the data which I need to write further code around. I will come back to you shortly - I think I can put it into a formula also now I have the logic around it. I have also worked out the date from a year/week using the above methodology. I will share it all when all done and tested to see if there are improvements or issues.


    Thank you again

    Sorry for not being clear, I thought I had been, honestly!



    By first step I meant I had no idea how to solve it at all, now I do I just need to put this code into a formula. You helped me with the approach so am very grateful. I still need to put this into a formula but hopefully I can do that via nested if. Once done, I then need to do the reversal of week and year to give a date.

    Well I did want a formula but I just wanted to solve it without using a table. I thought the above would help.


    Next step is now to use that logic above to make it into a single cell formula (with a reference to one cell for the date to convert). I also need to work it back from the week-Year to give a date using the same logic in reverse (some sheets just have the week-year, but I need to convert into a date for calculations).


    Sorry if it seems a messy way of doing things, but there is a lot of work that im "finding"

    Ok so this is what I came up with. It seems to match the table until 2030. Now I need to work out the reverse (ie put in week number and year and it will give me the date of the Saturday - this is because they have reports in this format but for excel to calculate I actually need the dates otherwise it gets messy). The key to the below was when the date was bigger than the last Saturday because you then have to add a year.



    Hello,


    I am just playing with it now. However, I think there may be an issue giving the correct year (especially if the date is at the end of December because the last Saturday may be before the date put in but it will be week 1 of the following year). The bigger question is why they don't follow the WEEKNUM function but there we go! Perhaps it is just one of those things that sound easier to do than reality

    Hello Carim


    Thank you for responding. The attachment should show a date in column B and the corresponding Week in column E done as a simple table. What I want to do is put in any date and it tell me the corresponding week and year. For instance, if I type in 26/12/2020, it would give me a week Number 1, Year 2021 ("01-2021") as the result. I want this as a formula so I can put in any date and get the correct week number-Year combo without using a table (the result is shown in column J). I don't want to build a function because this logic would be used across multiple books. From what you have sent I think I can get the result I want, I just need to change the reference date with some additional formula to link with the date in cell A7(because it will always be the last Saturday of that year).

    Once you have tested the formula ... feel free to comment

    Hello


    Sorry for the delay in responding - I had to work on another urgent project than holiday took hold! Happy New Year to you all!


    Ok so I have tested the formula, but it doesn't seem to work quite how I wanted. Basically I want to enter any date and give me the correct week number for that year using the logic of the Saturday after last Friday of the year is week 1.


    I didn't want to go through a lookup table as it should be relatively straight forward - and now it is bloody mindedness that im trying to solve it. On thinking again, I would also need the correct year calculated. Im starting to think VBA might be the only way forward. I have attached the table that is being used currently. What I wish to do is type in a "real date" and this give me the weeknumber and year.

    Files

    Thanks Roy. It doesn't seem to quite work as gives answer of 1 against any date (I replaced now with cell) the date values - it wasn't a million miles from where I was, but I think I need a nested if for week 53 (I don't think 54 is possible)


    =IF(WEEKNUM(DATE(YEAR(B2),12+1,0)+MOD(-WEEKDAY(DATE(YEAR(B2),12+1,0),2)-2,-7))=52,WEEKNUM(DATE(YEAR(B2),12+1,0)+MOD(-WEEKDAY(DATE(YEAR(B2),12+1,0),2)-2,-7))-51,WEEKNUM(DATE(YEAR(B2),12+1,0)+MOD(-WEEKDAY(DATE(YEAR(B2),12+1,0),2)-2,-7))-52)


    Looks like the ball park I was in though so will try an persevere.

    Hello all,


    Just had to reset to a new account as couldn't login. Anyway, I have an issue which im wondering someone can help.


    I need a formula to work out a custom week number whereby the Saturday AFTER last Friday in December is Week 1 of the year. Is there a formula that can derive this or do I have to use vba (I think I do a custom function in VBA).


    Pretty sure its a combo of weeknum/weekday/date and working out the days, but I haven't got it working perfectly. Friday 31/12/2021 would be week 53 for instance, but 01/01/2022 would be week 1. 25/12/20 would be week 52, Sat 29/12/18 would be week 1 etc etc. Can anyone help? Normally I solve it within an hour of asking for help so hoping the charm works again!