CODEWITHSUNDEEP
vbaexceldate
Jethro H
Jethro H

Reputation: 37

Trying to format date to save worksheet

I am looking to save an individual worksheet from a workbook with today's date in the filename. ex. (c:\HotDogS\sales\Daily_12_04_16.xslx)

The problem I am encountering is getting the date to show right in the filename. I have 1 cell that has the simple formula of =TODAY(). The cell is formatted for mm/dd/yy.

The formula I am trying to use to save the date for the sheet is: =left(B3,2)&"_"&mid(B3,4,2)&"_"&right(B3,2)

So I am expecting a date of 12/04/16 to come out as 12_04_16, but I am getting is, 42_08_08.

Can somebody shed some light on this??

Thank you!!

Upvotes: 1

Views: 233

Answers (2)

chris neilsen
chris neilsen

Reputation: 53127

Referencing a cell formatted as date returns the underlying date serial number.

To get a date formatted as you want use

=TEXT(B3,"dd\_mm\_yy")

Upvotes: 1

YowE3K
YowE3K

Reputation: 23974

Assuming TODAY is 4 December 2016, i.e. serial day number 42708, your formula is:

=left(B3,2)&"_"&mid(B3,4,2)&"_"&right(B3,2)

Left(B3,2) is 42. Mid(B3,4,2) is 08. Right(B3,2) is 08.

So your final result is 42_08_08.

You probably want to use (as an Excel formula):

=TEXT(B3,"mm\_dd\_yy")

Or in VBA you could use

Format(Range("B3").Value, "mm_dd_yy")

Excel stores dates as the number of days (and fractions of a day) since 0 January 1900. Therefore

  • 1 January 1900 is day 1
  • 31 January 1900 is day 31
  • 1 February 1900 is day 32
  • 29 February 1900 (even though it doesn't exist - but the bug has been maintained for backward compatibility) is day 60
  • 1 January 1901 is day 367
  • 4 December 2016 is day 42708
  • NOW() is day 42709.328 (it's about 7:52am on 5 December 2016 at the moment)

Upvotes: 1

Related Questions