×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here
Home > Forums > Programmers > Languages > VBA Visual Basic for Applications (Microsoft) > FAQs

VBA Visual Basic for Applications (Microsoft) FAQ

Excel How To

Capturing Date and Workbook Related Information In Excel Using Formulae and VBA by Harlequin007
faq707-5039
Posted: 15 Apr 04

Just a sample of some of the ways you can capture the current date, time filename, worksheet name and other associated data within Microsoft Excel.

Samples Appear like This
Actual Code or Formula Appears Like This

15/4/2004 14:23
=NOW()

April 15, 2004
=TEXT(NOW(),"mmmm dd, yyyy")

April 15, 2004 - 14:23
=TEXT(NOW(),"mmmm dd, yyyy - hh:mm")

2:23 p.m.  April 15, 2004
=" "&RIGHT("  "&FIXED(IF(HOUR(NOW())=0,12,IF(HOUR(NOW())>12,HOUR(NOW())-12,HOUR(NOW()))),0,TRUE),2)&":"&RIGHT("00"&FIXED(MINUTE(NOW()),0,TRUE),2)&IF(HOUR(NOW())<12," a.m."," p.m.")&"    "&CHOOSE(MONTH(NOW()),"January","February","March","April","May","June","July","August","September","October","November","December")&" "&RIGHT("00"&FIXED(DAY(NOW()),0,TRUE),2)&", "&FIXED(1900+(YEAR(NOW())-1900),0,TRUE)

April 15, 2004   2:23 p.m.
=CHOOSE(MONTH(NOW()),"January","February","March","April","May","June","July","August","September","October","November","December")&" "&RIGHT("00"&FIXED(DAY(NOW()),0,TRUE),2)&", "&FIXED(1900+(YEAR(NOW())-1900),0,TRUE)&"     "&RIGHT("  "&FIXED(IF(HOUR(NOW())=0,12,IF(HOUR(NOW())>12,HOUR(NOW())-12,HOUR(NOW()))),0,TRUE),2)&":"&RIGHT("00"&FIXED(MINUTE(NOW()),0,TRUE),2)&IF(HOUR(NOW())<12," a.m."," p.m.")

2:23 PM
=TEXT(NOW(),"[$-409]h:mm AM/PM;@")

2:23 PM April 15, 2004
=TEXT(NOW(),"[$-409]h:mm AM/PM;@")&" "&TEXT(NOW(),"mmmm d, yyyy")

April 15, 2004 2:23 PM
=TEXT(NOW(),"mmmm dd, yyyy")&" "&TEXT(NOW(),"[$-409]h:mm AM/PM;@")

April 15: 2004 2:23:53 PM
=TEXT(NOW(),"mmmm dd, yyyy")&" "&TEXT(NOW(),"[$-409]h:mm:ss AM/PM;@")

13/4/2004 14:18
    Sheets("DWG_02").Range("VBA_Time") = Now()

7/4/2004 15:21


Worksheet Name
=MID(CELL("filename",A13),FIND("]",CELL("filename",A13))+1,255)

Workbook Name
=MID(CELL("FILENAME",F23),FIND("[",CELL("FILENAME",F23))+1,FIND("]",CELL("FILENAME",F23))-FIND("[",CELL("FILENAME",F23))-1)

Workbook Pathname
=MID(CELL("FILENAME",F26), 1,FIND("[",CELL("FILENAME",F26))-1)

Workbook Pathname with Workbook Filename Included
=MID(CELL("FILENAME",F27), 1,FIND("]",CELL("FILENAME",F27)))

Workbook Pathname with Workbook Filename and Worksheet Included
=CELL("FILENAME",F30)

Hope those few pointers help someone

Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close