1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Show the current week according to today's date

Discussion in 'VBA Macros' started by Visor, Dec 4, 2017.

  1. Visor

    Visor Member

    Messages:
    157
    Dear friends of the forum how can be achieved to initialize the userform show in labels the week according to today's date, I have tried something but it generates error
    upload file
    Thanks in advances

    Attached Files:

  2. vletm

    vletm Well-Known Member

    Messages:
    2,888
    Visor
    as 'SEMENA' is for week then You could do this way:
    Code (vb):

    Private Sub UserForm_Initialize()
        LbHoy.Caption = Format(Date, "dddd, dd/mm/yy")
        Label13.Caption = Format(Date, "ww", vbMonday, vbFirstFourDays)
    End Sub
     
  3. Visor

    Visor Member

    Messages:
    157
    Thanks vletm, but I mean the week number in the database, I guess that requires finding the date first according to the list that is in column B and showing the previous cell next to it
  4. vletm

    vletm Well-Known Member

    Messages:
    2,888
    Visor
    1st You wrote something with UserForm ...
    and You meant something with the database ...
    Do You mean to ACAD-sheet?

    Could You test to use Weeknum-function?
    to cells [A6] ... =Weeknum(B6,1) or ,2) ... depends Your weeks
  5. Visor

    Visor Member

    Messages:
    157
    Thanks, Vletm if in the ACAD sheet in column A are the weeks of row 9 and in column B are the dates of the same row 9.
    that is, here it seems that I must first find the date and that the corresponding cell is shown next to column A.
    I do not understand the suggested weeknum
    Last edited: Dec 4, 2017
  6. Visor

    Visor Member

    Messages:
    157
    depend of my weeks Column A
  7. vletm

    vletm Well-Known Member

    Messages:
    2,888
    Could You try to write
    what do You would like to get and where? ... I know that NOT in UserForm!
    Those are as clear as message that I got:
    Gracias, Vletm si en la hoja de ACAD en la columna A están las semanas de la fila 9 y en la columna B son las fechas de la misma fila 9.
    es decir, aquí parece que primero debo encontrar la fecha y que se muestra la celda correspondiente junto a la columna A.

    I ... understood two words!
  8. Visor

    Visor Member

    Messages:
    157
    Yes vletm, I am very grateful to stay on my topic.
    I use translation technology by google, I'm sorry ..
    Well, for example today we are date to sea. 5-Dec-17 (this is in column B, row 45) this must be found, then it must show in the userform the cell of column A of the same row 45, ... that is to say week 8
    All the dates of this week will be shown as week 8, next week will show them as 9
    Thank you for your collaboration

    Sheet("ACAD")
    upload_2017-12-5_9-10-45.png

    Userform
    upload_2017-12-5_9-8-36.png
  9. vletm

    vletm Well-Known Member

    Messages:
    2,888
    hmm ... 8 ?
    Somewhere somebody marks with some rules that now would be week 8...hmm?
    For me ... now is weeknum 49!
    Somewhere week starts from Sunday ... for me from Monday.
    If no clear rule of this case ... then ... challenge!
    or
    Do You 'just' would like to get 'value' from Sheet("ACAD") left side from that date, no matter what would be 'correct .. universal .. normal' week number,
    nothing more or less?
    ... and should that work ONLY if date, present in UserForm, would be as TODAY?
  10. Visor

    Visor Member

    Messages:
    157
    :DD, well,... what happens is that every academic semester (there are not 6 literal months of study because there are additional activities but, after 18 weeks of classes), it starts on a certain date and it is not always the same, we have 18 weeks to complete that semester of classes. That's why I started counting the week since October 16, and then I registered the dates as a timeline.
    I need to initialize the userform shows the week arranged in column A from the A9 to the last row according to the date in which we are now
  11. vletm

    vletm Well-Known Member

    Messages:
    2,888
    ... as written in reply #7!
    If You cannot ... there would be a challenge and then Good Luck!
  12. Visor

    Visor Member

    Messages:
    157
    I am very grateful, for your words, I will continue reviewing
  13. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,786
    See if attached is what you want.

    Just curious, what happens if you need to open this on Saturday or Sunday as these days have been excluded in your list.

    Attached Files:

    Thomas Kuriakose and Visor like this.
  14. Visor

    Visor Member

    Messages:
    157
    Thank you Shrivallabha, I had tried the challenge but I have not found the way to solve, even your code I have not seen before, it is ok how the problem has been solved, we find the date in column B and the number that is in cell of column A is shown next to the contracted date.
    In reality these weeks correspond to working days, here the working days are from Monday to Friday, on Saturday and Sunday tourism plans are made.
    On the other hand, suppose the case of growth control of animals, chickens, pigs, cattle, etc. the week in which these are born is week 1. Both the control of growth and the control of diseases is done in weeks of 7 days (Monday to Sunday), here if it is considered Saturday and Sunday
    In the case of the production in a factory there, the weeks of the year are considered to establish the production lots and the concept of Chihiro would serve me
    I thank you very much for the solution I hope that the other semas the label shows 9 because I do not want to now urgar in the configuration of the computer because I have automatic files.
    Sorry if the translation is not so good
    Thank you
    Solved theme
  15. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,786
    It is absolutely OK as I understood what you meant. And I understand that it is solved for you now.

    If not, you can post back by using screenshots/pictures if you feel words do not convey what you mean.

Share This Page