Day Function Format VBA

vbanewbie68

New Member
Joined
Oct 16, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Attachments

  • Day function VBA issue.png
    Day function VBA issue.png
    149.7 KB · Views: 9

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,159
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 16, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,159
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 16, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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




DateCreatedNameGift 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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,159
Office Version
  1. 365
Platform
  1. Windows
Try changing this line of code:
VBA Code:
Import.Cells(x, GiftDayofMonth).Value = Cells(x, DateCreated).Value
to this:
VBA Code:
Import.Cells(x, GiftDayofMonth).Value = Day(Cells(x, DateCreated).Value)
 
Solution

vbanewbie68

New Member
Joined
Oct 16, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Try changing this line of code:
VBA Code:
Import.Cells(x, GiftDayofMonth).Value = Cells(x, DateCreated).Value
to this:
VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,159
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 16, 2021
Messages
45
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,159
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,390
Messages
5,746,432
Members
424,016
Latest member
Shizzl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top