Hello,
I found this thread:
http://www.mrexcel.com/forum/showthread.php?t=86297
Then after using the code as described:
in a standard module
Code:
Dim SchedRecalc As Date
Sub Recalc()
Dim wbk As Workbook
Dim ws As Worksheet
Set wbk = ThisWorkbook
Set ws = wbk.Sheets("Sheet1")
ws.Range("C3").Value = Format(Now, "dd-mmm-yy")
ws.Range("C4").Value = Format(Time, "hh:mm:ss AM/PM")
Call SetTime
End Sub
Sub SetTime()
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "Recalc"
End Sub
Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="Recalc", Schedule:=False
End Sub
In thisworkbook module
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Disable
End Sub
It did indeed place the date and time into cells C3, C4
Then below that in cell C5 contains day/time of Holiday
6/9/2012 12:00:00 PM
Custom formatted to
[$-409]m/d/yy h:mm:ss AM/PM;@
Cell C6 contains the formula to find the difference:
=INT(C5-(TEXT(C3,"m/dd/yy")&" "&TEXT(C4,"h:mm:ss AM/PM")))&" Days, "&TEXT(RIGHT(C5-(TEXT(C3,"m/dd/yy")&" "&TEXT(C4,"h:mm:ss AM/PM")),LEN(C5-(TEXT(C3,"m/dd/yy")&" "&TEXT(C4,"h:mm:ss AM/PM")))-FIND(".",C5-(TEXT(C3,"m/dd/yy")&" "&TEXT(C4,"h:mm:ss AM/PM")),1)+1),"hh:mm:ss")&" Hours!"
Finally I inserted a shape (rectangle). Click on the shape and in the formula bar, type =C6 and press enter. Change the font and size and also the size of the rectangle to make it as big as possible.
I suppose this could also be done using a userform or something better than a rectangle, maybe it will get you started.
Here is what cells c3:c6 look like:
Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>C</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">7-Apr-12</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">3:32:23 PM</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">6/9/12 12:00:00 PM</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>62 Days, 20:27:37 Hours!</TD></TR></TBODY></TABLE>
Sheet1
<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">
Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C6</TH><TD style="TEXT-ALIGN: left">=INT(
C5-(TEXT(C3,"m/dd/yy")&" "&TEXT(C4,"h:mm:ss AM/PM")))&" Days, "&TEXT(
RIGHT(C5-(TEXT(C3,"m/dd/yy")&" "&TEXT(C4,"h:mm:ss AM/PM")),LEN(C5-(TEXT(C3,"m/dd/yy")&" "&TEXT(C4,"h:mm:ss AM/PM")))-FIND(".",C5-(TEXT(C3,"m/dd/yy")&" "&TEXT(C4,"h:mm:ss AM/PM")),1)+1),"hh:mm:ss")&" Hours!"</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
