Capture date with only month and year, disregard day

Hello all, thanks in advance for the help.

I'm trying to capture a date using VBA in a specific format while disregarding the day. For example...

On the spreadsheet, I'll have the date 10/1/2015. I would like to capture that date in a variable as Oct-15. When I call it up later, I want it to still be Oct-15.

I'm running into a peculiar issue... I capture the date using the same code in 2 different lines and then messagebox the value stored and I get the date in 2 different formats. Here is the code...

Please Login or Register  to view this content.

For SD1 the messagebox displays Oct-15
For SD2 the messagebox displays 10/15/2015

I only did that because I wasn't getting a consistent result. Can anyone help me with this?

My ultimate goal is to capture and use all of the dates as only month and year. Completely ignoring the day portion of the date.

Thanks!

Hi

It depends on how you want to use the data.

Dates are actually numbers in excel, so there is no such thing in excel as a date that has no associated day. You could do one of three things, depending on how you want to use the data:

- treat every date as the first of the month (allows you to perform calculations on the whole date)
- return the month and year part of the date separately (allows you to perform separate operations on the month and year)
- create a string comprising only the month and year with no day (treats all dates in a particular month and year as identical)

this macro illustrates the three approaches, assuming your date is in cell A1:

Please Login or Register  to view this content.

Thanks for the reply. Actually, I do have both of them formatted as dates. That line of code looks like this...

Please Login or Register  to view this content.

In the example you gave, I'm guessing you can only get the date as numbers, no month abbreviation. Is that correct?

Hi NickyC,
Option 3 works best for what I'm trying to do and I put it into my code and it seems to do what I'm looking for. Now to add a twist. If I want to take that date and then use it as the start of a list of consecutive months, how would I handle that?

Thanks!

NickyC,
It's kinda hard to explain what I'm trying to do. I have a list of items that start on a specific date. I want to create a timeline at the right side of the list that starts at the earliest date from the list and then adds a month as you move to the next cell.

Once that timeline is in place, then I want to go back to each item on the list, find it's place on the timeline and start pasting values on the timeline for the list items. Attached is a sample spreadsheet showing what I'm trying to do along with my code. Hopefully it makes some sense.

I tried using the 3 methods that you gave and thought that changing to the 1st of each month was the best way to go. But, I tried using your code and it keeps giving me the 15th of the month instead of the 1st of the month. Perhaps it's due to regional date differences or something like that. Any help would be greatly appreciated.

Thanks for all the help. I ended up capturing the month and year separately (the way NickyC suggested) and putting them in individual cells rather than trying to combine them into 1 cell. Then, when doing the comparisons I would compare for matches in both cells. That way simplified things and I was able to get the result I was looking for.