Day Function Format VBA

[vbanewbie68]

Hi Sir or Madam
I am new to VBA in excel. I need your help please to see how to use the Day function VBA to focus on ‘Day’ only, rather than in the month and year format. Your help is much appreciated.

Please see some screenshots below – first one is my code for a test, and then the 2nd screenshot is the spreadsheet - Take a look at the Gift Day of the Month Column where I need to have just Day only, rather than the full format of date. I hope everything makes sense.

Best regards
vbanewbie68

 

[Joe4]

Welcome to the Board!

The Day function in VBA works the same way the Day function in Excel works.
What part is tripping you up?

See here: MS Excel: How to use the DAY Function (WS, VBA)

Also, when posting your VBA code, please do not paste images of it. Please copy and paste the code using Code Tags. That allows us to easily copy your code on our side and work with it.
See here for details on that: How to Post Your VBA Code

 

[vbanewbie68]

Welcome to the Board!

The Day function in VBA works the same way the Day function in Excel works.
What part is tripping you up?

See here: MS Excel: How to use the DAY Function (WS, VBA)

Also, when posting your VBA code, please do not paste images of it. Please copy and paste the code using Code Tags. That allows us to easily copy your code on our side and work with it.
See here for details on that: How to Post Your VBA Code

Hi Joe4
Thanks for the message.
Please take a look my codes below and then the question is that how and where to I need to add Day function? Will it be this? Import.Cells(x, GiftDayofMonth).Value = Cells(x, DateCreated).Value




Sub Add_Columns()

Import.Cells(1, 1).End(xlToRight).Select ' To illustrate what this range refers to
LastColumn = Import.Cells(1, 1).End(xlToRight).Column

Range(Cells(1, LastColumn + 1), Cells(1, LastColumn + 1)).Select ' to illustrate what this range refers to
Range(Cells(1, LastColumn + 1), Cells(1, LastColumn + 1)).Value = Array("Gift Day of Month")

End Sub

Sub Add_Rows_Data()



LastRow = Import.Cells(1, 1).End(xlDown).Row

GiftDayofMonth = WorksheetFunction.Match("Gift Day of Month", Import.Cells(1, 1).EntireRow, 0)

DateCreated = WorksheetFunction.Match("DateCreated", Import.Cells(1, 1).EntireRow, 0)


For x = 2 To LastRow


Import.Cells(x, GiftDayofMonth).Value = Cells(x, DateCreated).Value




Next x

End Sub

 

[Joe4]

You haven't told us exactly what it is you are trying to do with the DAY function.
We can easily get the DAY from the date column, but then what exactly are you doing with that?

Also, you seem to be missing some VBA code. Your code makes a reference to a sheet variable named "Import", but I do not see this being set anywhere in your code.

 

[vbanewbie68]

You haven't told us exactly what it is you are trying to do with the DAY function.
We can easily get the DAY from the date column, but then what exactly are you doing with that?

Also, you seem to be missing some VBA code. Your code makes a reference to a sheet variable named "Import", but I do not see this being set anywhere in your code.

See below my screenshot. As a test to find out how Day Function works on VBA.

What I did was that I managed to get VBA to copy DateCreated's column values and it added the values automatically onto Gift Day of Month which is great.

Now on the Gift Day of Month column values which has full date format. I want to change it to just Day only.

Does it make sense to you?

Regards

Rej

DateCreated	Name	Gift Day of Month
17/09/2021 15:44​	Test Dummy 1	17/09/2021 15:44​
15/09/2021 15:47​	Test Dummy 2	15/09/2021 15:47​
14/09/2021 19:24​	Test Dummy 3	14/09/2021 19:24​
13/09/2021 13:40​	Test Dummy 4	13/09/2021 13:40​
08/09/2021 15:41​	Test Dummy 5	08/09/2021 15:41​
08/09/2021 15:32​	Test Dummy 6	08/09/2021 15:32​
06/09/2021 18:26​	Test Dummy 7	06/09/2021 18:26​
06/09/2021 14:50​	Test Dummy 8	06/09/2021 14:50​
06/09/2021 10:57​	Test Dummy 9	06/09/2021 10:57​
03/09/2021 11:18​	Test Dummy 10	03/09/2021 11:18​
03/09/2021 11:17​	Test Dummy 11	03/09/2021 11:17​
01/09/2021 11:46​	Test Dummy 12	01/09/2021 11:46​
01/09/2021 11:44​	Test Dummy 13	01/09/2021 11:44​

 

[Joe4]

Try changing this line of code:

Import.Cells(x, GiftDayofMonth).Value = Cells(x, DateCreated).Value

to this:

Import.Cells(x, GiftDayofMonth).Value = Day(Cells(x, DateCreated).Value)

 

[vbanewbie68]

Try changing this line of code:

Import.Cells(x, GiftDayofMonth).Value = Cells(x, DateCreated).Value

to this:

Import.Cells(x, GiftDayofMonth).Value = Day(Cells(x, DateCreated).Value)

Hi Joe4

The result has passed! It works perfectly. Thank you for your kind help on this matter which is much appreciated!

Best regards

vbanewbie68

 

[Joe4]

You are welcome!
Glad I was able to help.

All we needed to do was to find which line of code was populating that column, and wrap it in the DAY function.
(Hope that makes sense!)

 

[vbanewbie68]

You are welcome!
Glad I was able to help.

All we needed to do was to find which line of code was populating that column, and wrap it in the DAY function.
(Hope that makes sense!)

Hi
Is it possible to ask you another question in relation to VBA?
Regards

 

[Joe4]

Is it possible to ask you another question in relation to VBA?

If it is a new question, it is best to ask it in a new thread.
That way if I do not know the answer or am not available at the time to answer it, others will see it as a new unanswered question in the "Unanswered threads" list, and your chances of receiving assitance increase greatly.