Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Capturing Date and Workbook Related Information In Excel Using Formulae and VBA
Excel How To
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.
[color green]Samples Appear like This[/color]
[highlight]Actual Code or Formula Appears Like This[/highlight]
[color green]15/4/2004 14:23[/color]
[Highlight]=NOW()[/highlight]
[color green]April 15, 2004[/color]
[Highlight]=TEXT(NOW(),"mmmm dd, yyyy")[/highlight]
[color green]April 15, 2004 - 14:23[/color]
[Highlight]=TEXT(NOW(),"mmmm dd, yyyy - hh:mm")[/highlight]
[color green] 2:23 p.m. April 15, 2004[/color]
[Highlight]=" "&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)[/highlight]
[color green]April 15, 2004 2:23 p.m.[/color]
[Highlight]=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.")[/highlight]
[color green]2:23 PM[/color]
[Highlight]=TEXT(NOW(),"[$-409]h:mm AM/PM;@")[/highlight]
[color green]2:23 PM April 15, 2004[/color]
[Highlight]=TEXT(NOW(),"[$-409]h:mm AM/PM;@")&" "&TEXT(NOW(),"mmmm d, yyyy")[/highlight]
[color green]April 15, 2004 2:23 PM[/color]
[highlight]=TEXT(NOW(),"mmmm dd, yyyy")&" "&TEXT(NOW(),"[$-409]h:mm AM/PM;@")[/highlight]
[color green]April 15: 2004 2:23:53 PM[/color]
[highlight]=TEXT(NOW(),"mmmm dd, yyyy")&" "&TEXT(NOW(),"[$-409]h:mm:ss AM/PM;@")[/highlight]
[color green]13/4/2004 14:18[/color]
[highlight] Sheets("DWG_02").Range("VBA_Time") = Now()[/highlight]
[color green]7/4/2004 15:21[/color]
[highlight][/highlight]
[color green]Worksheet Name[/color]
[highlight]=MID(CELL("filename",A13),FIND("]",CELL("filename",A13))+1,255)[/highlight]
[color green]Workbook Name[/color]
[highlight]=MID(CELL("FILENAME",F23),FIND("[",CELL("FILENAME",F23))+1,FIND("]",CELL("FILENAME",F23))-FIND("[",CELL("FILENAME",F23))-1)[/highlight]
[color green]Workbook Pathname[/color]
[highlight]=MID(CELL("FILENAME",F26), 1,FIND("[",CELL("FILENAME",F26))-1)[/highlight]
[color green]Workbook Pathname with Workbook Filename Included[/color]
[highlight]=MID(CELL("FILENAME",F27), 1,FIND("]",CELL("FILENAME",F27)))[/highlight]
[color green]Workbook Pathname with Workbook Filename and Worksheet Included[/color]
[highlight]=CELL("FILENAME",F30)[/highlight]
![[colorface]](/data/assets/smilies/colorface.gif "[colorface] [colorface]")
Hope those few pointers help someone
[color green]Samples Appear like This[/color]
[highlight]Actual Code or Formula Appears Like This[/highlight]
[color green]15/4/2004 14:23[/color]
[Highlight]=NOW()[/highlight]
[color green]April 15, 2004[/color]
[Highlight]=TEXT(NOW(),"mmmm dd, yyyy")[/highlight]
[color green]April 15, 2004 - 14:23[/color]
[Highlight]=TEXT(NOW(),"mmmm dd, yyyy - hh:mm")[/highlight]
[color green] 2:23 p.m. April 15, 2004[/color]
[Highlight]=" "&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)[/highlight]
[color green]April 15, 2004 2:23 p.m.[/color]
[Highlight]=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.")[/highlight]
[color green]2:23 PM[/color]
[Highlight]=TEXT(NOW(),"[$-409]h:mm AM/PM;@")[/highlight]
[color green]2:23 PM April 15, 2004[/color]
[Highlight]=TEXT(NOW(),"[$-409]h:mm AM/PM;@")&" "&TEXT(NOW(),"mmmm d, yyyy")[/highlight]
[color green]April 15, 2004 2:23 PM[/color]
[highlight]=TEXT(NOW(),"mmmm dd, yyyy")&" "&TEXT(NOW(),"[$-409]h:mm AM/PM;@")[/highlight]
[color green]April 15: 2004 2:23:53 PM[/color]
[highlight]=TEXT(NOW(),"mmmm dd, yyyy")&" "&TEXT(NOW(),"[$-409]h:mm:ss AM/PM;@")[/highlight]
[color green]13/4/2004 14:18[/color]
[highlight] Sheets("DWG_02").Range("VBA_Time") = Now()[/highlight]
[color green]7/4/2004 15:21[/color]
[highlight][/highlight]
[color green]Worksheet Name[/color]
[highlight]=MID(CELL("filename",A13),FIND("]",CELL("filename",A13))+1,255)[/highlight]
[color green]Workbook Name[/color]
[highlight]=MID(CELL("FILENAME",F23),FIND("[",CELL("FILENAME",F23))+1,FIND("]",CELL("FILENAME",F23))-FIND("[",CELL("FILENAME",F23))-1)[/highlight]
[color green]Workbook Pathname[/color]
[highlight]=MID(CELL("FILENAME",F26), 1,FIND("[",CELL("FILENAME",F26))-1)[/highlight]
[color green]Workbook Pathname with Workbook Filename Included[/color]
[highlight]=MID(CELL("FILENAME",F27), 1,FIND("]",CELL("FILENAME",F27)))[/highlight]
[color green]Workbook Pathname with Workbook Filename and Worksheet Included[/color]
[highlight]=CELL("FILENAME",F30)[/highlight]
Hope those few pointers help someone