+ Reply to Thread
Results 1 to 12 of 12

Date & Day issue

  1. #1
    Registered User
    Join Date
    01-18-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    87

    Date & Day issue

    Hi gurus,

    hope you are well. Happy new year to you all.

    very quick question : please can someone help me with the coding. I'm trying to achieve the following to have date & day.

    - In Cell A1 -> Month there i.e. January
    - I want in Cell B3 to B32- auto fill each date in dd/mm/yy format i.e. from 01/01/2020 to 31/01/2020
    - I want in Cell B4 to B34 - it auto fill days for the above dates
    However, it should stops after the last day of the month.

    I'm sure for all of you it's the simplest thing and I'd really appreciate your help.

    Many thanks,
    abac

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    2007
    Posts
    3,442

    Re: Date & Day issue

    Hi abac,

    Assuming the month in cell A1 is text and not a date formatted as a month and you want dates in Col. B and days in Col. C (or else the days will overwrite the dates) this should do the job:

    Please Login or Register  to view this content.
    Note too that the year used is from today's date.

    HTH

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  3. #3
    Registered User
    Join Date
    01-04-2020
    Location
    Hull, UK
    MS-Off Ver
    2016
    Posts
    60

    Re: Date & Day issue

    This should do what you want. Works from a blank Worksheet

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Last edited by juddaaaa; 01-05-2020 at 01:19 AM.

  4. #4
    Registered User
    Join Date
    12-19-2019
    Location
    mi, usa
    MS-Off Ver
    2013
    Posts
    12

    Re: Date & Day issue

    if it works for you this can be done with just formulas on worksheet you don't need to use vb
    attached example where you enter month and year column B will fill with days of the month entered

    enter in B1 =IF($A$3<>"",IF($A$2<>"",DATE(A3,A2,1),""),"")
    enter in B2 =IF(B1="","",IF(MONTH(B1+1)<>MONTH(B1),"",B1+1))
    copy formula from B2 down to B32

    enter in A1 =IF(OR(A2<1,A2>12),"",CHOOSE(A2,"January","February","March","April","May","June","July","August","September","October","November","December"))
    enter month (number) in A2
    enter year in A3

    hope it helps
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-18-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    87

    Re: Date & Day issue

    thank you so much for your help - it doesn't give me days for those dates. Any idea how to do that ?

  6. #6
    Registered User
    Join Date
    01-18-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    87

    Re: Date & Day issue

    Quote Originally Posted by juddaaaa View Post
    This should do what you want. Works from a blank Worksheet

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Thank you for your help.
    For some reason it's not working, I'm not sure if I'm doing anything wrong.
    Please can you help me.
    Thanks

  7. #7
    Registered User
    Join Date
    01-18-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    87

    Re: Date & Day issue

    Quote Originally Posted by Trebor76 View Post
    Hi abac,

    Assuming the month in cell A1 is text and not a date formatted as a month and you want dates in Col. B and days in Col. C (or else the days will overwrite the dates) this should do the job:

    Please Login or Register  to view this content.
    Note too that the year used is from today's date.

    HTH

    Robert
    Hi Robert,

    Thank you for your help.

    I have copied the formula in VBA but for some reason it's not doing anything. The file is saved as XLSM and everythign is there but nothing is coming up.
    please can you take a look.
    many thanks

  8. #8
    Registered User
    Join Date
    12-19-2019
    Location
    mi, usa
    MS-Off Ver
    2013
    Posts
    12

    Re: Date & Day issue

    to get full day name enter =TEXT(B1,"dddd") in cell C1 then copy formula from C2 down to C32
    to get abbreviated day name enter =TEXT(B1,"ddd") in cell C1 then copy formula from C2 down to C32

  9. #9
    Registered User
    Join Date
    01-04-2020
    Location
    Hull, UK
    MS-Off Ver
    2016
    Posts
    60

    Re: Date & Day issue

    Have a look at this example. Clicking the button in the worksheet will fill in the sheet.

    You can see how I've placed the codes in the Modules in the VBA editor.
    Attached Files Attached Files

  10. #10
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,248

    Re: Date & Day issue

    Quote Originally Posted by dav2020 View Post
    =IF(OR(A2<1,A2>12),"",CHOOSE(A2,"January","February","March","April","May","June","July","August","September","October","November","December"))
    You might find this interesting... there is a shorter formula available that will do the same as your formula above.

    =IF(OR(A2<1,A2>12),"",TEXT(29*A2,"mmmm"))

  11. #11
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,248

    Re: Date & Day issue

    .
    And you might find these interesting as well (although I think you might already know them)...


    Quote Originally Posted by Trebor76 View Post
    intMonth = Evaluate("VLOOKUP(A1,{""January"",1;""February"",2;""March"",3;""April"",4;""May"",5;""June"",6;""July"",7;""August"",8;""September"",9;""October"",10;""November"",11;""December"",12},2,0)")
    Much shorter...

    intMonth = [MONTH(A1&1)]


    Quote Originally Posted by Trebor76 View Post
    intMonthDays = Day(DateSerial(Year(Now()), intMonth + 1, 1) - 1)
    A smidgeon shorter...

    intMonthDays = Day(DateSerial(Year(Now), intMonth + 1, 0))

    By the way, your macro can be replaced with this shorter one (where I have chosen to output the day name instead of the day number in Column C)...
    Please Login or Register  to view this content.
    Last edited by Rick Rothstein; 01-06-2020 at 02:34 PM.

  12. #12
    Registered User
    Join Date
    01-18-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    87

    Re: Date & Day issue

    you guys are amazing.
    Thank you so much

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. NETWORKDAYS.INTL issue when start date on weekdays end date on weekend
    By Faridwahidi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-07-2017, 11:15 PM
  2. [SOLVED] Date format issue when using date in a fomula
    By FieldHaven in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-27-2016, 04:57 PM
  3. Replies: 0
    Last Post: 11-16-2015, 08:57 AM
  4. Replies: 6
    Last Post: 04-20-2015, 09:40 AM
  5. VLOOKUP: Date vs. Date Issue
    By civitarl in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-31-2015, 07:16 PM
  6. [SOLVED] Issue with date format when comparing two date values (I'm in Australia)
    By aaron.irvine in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-08-2013, 01:13 AM
  7. Date Picker Date Format issue
    By AndyE in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-21-2012, 06:43 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1