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
|
Talk To Other Members