Jump to content


Getting an "Insecure Connection" warning for Exisle? No worry

Details in this thread

Anyone good with Excel VBA? Need help on a project

Extract and convert dates

  • Please log in to reply
1 reply to this topic

#1 Darkside_1

Darkside_1

    Your friendly Neighborhood Moderator.

  • Moderator
  • 3,086 posts

Posted 24 March 2018 - 11:39 AM

Hi,

I am having some trouble with extracting and converting dates in VBA

I have a project where I am building a date range to be used as part of a file name for the
generated output report.  The project is an employee timesheet for payroll where the employee
puts in the time and hours they worked.

The output filename is a concatenation in the format of a Report Title - Employee Name - "Date From"2018 to "Date To"2018.xlsx

Example below: (Note: Actual Report title and employee name are substituted to protect privacy)

Timesheet - John Doe 03112018 to 03242018.xlsx

This so far works fine except, I'd like to have the month as a short alphabetic (i.e. Jan, Feb, Mar, etc) and the order of the date to appear as ddmmmyyyy (i.e. 11Mar2018)

On my sheet, the dates for the daily data entry are in Column A from rows 11 - 17 (for the first week) and 19 to 25 (for the second week)

The dates here are formatted as dd-mmm (i.e. 11-Mar) as I dont' want the year showing in these cells.

To build the start and end dates, I wish to build from cells A11 and A25 (First and last dates of the period) where for example, A11 is 11-Mar and A25 is 24-Mar

From these two cells, I'd like to extract the month and the day as their values and convert the month to the short Alphabetic format so I could then have the filename outputted as:
Timesheet - John Doe - 11Mar2018 to 24Mar2018.xlsx


This is the contatination sequence so far that works but outputs the dates as
03112018 to 03242018.xls


NewFileName = "Timesheet - " & wb.Sheets("Timesheet Preview").Range("B5").Value _
& " - " & Left(Range("A11").Value, 2) & Mid(Range("A11").Value, 4, 2) & "2018 to " _
& Left(Range("A25").Value, 2) & Mid(Range("A25").Value, 4, 2) & "2018.xlsx"

Cell B5 holds the name of the employee that will be referenced

Cell A11 is the beginning date for the range i.e. 11-Mar

Cell A25 is the end date of the range i.e. 24-Mar

What can be done to modify the concatenation?

Do I need to declare any other variables as place-holders for the month and days to be used to reformat them for the output?

Anything you can suggest would be appreciated; otherwise I can keep the format as is and manually rename the file when it saves.

Regards,

Darkside_1

"Harry S. Truman said that he felt like a bale of hay fell on him.

I feel like I got the whole damn barn."

Darren "Condor" McGavin - "By Dawn's Early Light"

HBO TV Movie - 1990


*** (Click here to view my signature) ***

Posted Image

"A pound of supplies taken from your enemy is worth 10 pounds of your own supplies"
Sun Tzu "The Art of War"


#2 Darkside_1

Darkside_1

    Your friendly Neighborhood Moderator.

  • Moderator
  • 3,086 posts

Posted 24 March 2018 - 11:46 AM

Never mind, I think I figured it out.  I believe my regional settings are set differently for short dates.  If I set the work PC's to the same parameters, then it should work as indicated.  I'll test that theory out when I'm back at the office tomorrow night.

When I tested my sheet out at home, it works fine on my system but the dates are formatted different when at the office.

"Harry S. Truman said that he felt like a bale of hay fell on him.

I feel like I got the whole damn barn."

Darren "Condor" McGavin - "By Dawn's Early Light"

HBO TV Movie - 1990


*** (Click here to view my signature) ***

Posted Image

"A pound of supplies taken from your enemy is worth 10 pounds of your own supplies"
Sun Tzu "The Art of War"



0 user(s) are browsing this forum

0 members, 0 guests, 0 anonymous users