Countdown 1 every workday

[Cimjet]

Hi Everyone.
I made this macro for fun, its based on a post last Sept.30.(Remove 1 from a
total after every day) answered by Ron Rosenfeld with a formula
(Networkdays).The post was on Answers.Microsoft.com
I do this for learning.I'm just at the learning stage.
I have a Date in A1 and a number in C1 that goes down by one everyday.
Now, my problem is, I want it just for Workdays..nor everydays.
I'm lost with this, can anyone help.

Option Explicit
Private Sub Workbook_Open()
Dim stdate As Date
Dim tday As Date
Dim ans As Integer
On Error GoTo Finish:
stdate = ActiveSheet.Range("A1")'Start Date
tday = Now()
ans = tday - stdate
If tday > stdate Then
Range("F1") = Range("C1") + 1 - ans
If Range("F1").Value <= 0 Then
Range("F1").Interior.ColorIndex = 3
MsgBox "Your Time has expired"
Else
Range("F1").Interior.ColorIndex = 0
End If
End If
Finish:
End Sub

Regards
Cimjet

 

[Don Guillett]

You will want to incorporate NETWORKDAYS (look in help)
in vba use
MsgBox Application.Run("ATPVBAEN.XLA!networkdays", [a7], [a8])

or if you, in the VBE ,do tools>refrences>atpvbaen then
MsgBox networkdays([a7], [a8])
=========================

 

[Cimjet]

Hi Don
Thank you, it works fine.
I have a question for you, I reference atpvbaen.xla in my Project VBA to make it
work, is this because Networkdays is not a standard formula, it's part of the
Analisis Toolpak, I'm using XL03.
Thanks again
Cimjet
You will want to incorporate NETWORKDAYS (look in help)
in vba use
MsgBox Application.Run("ATPVBAEN.XLA!networkdays", [a7], [a8])

or if you, in the VBE ,do tools>refrences>atpvbaen then
MsgBox networkdays([a7], [a8])
=========================

 

[Cimjet]

Typo: "Analysis Toolpak"
Just so you know, I tried help in VBA editor but got nothing , maybe I don't
know how to use it properly.
I usually highlight the word or words and press F1. Any comments on that.
Regards
Cimjet

Hi Don
Thank you, it works fine.
I have a question for you, I reference atpvbaen.xla in my Project VBA to make
it work, is this because Networkdays is not a standard formula, it's part of
the Analysis Toolpak, I'm using XL03.
Thanks again
Cimjet
You will want to incorporate NETWORKDAYS (look in help)
in vba use
MsgBox Application.Run("ATPVBAEN.XLA!networkdays", [a7], [a8])

or if you, in the VBE ,do tools>refrences>atpvbaen then
MsgBox networkdays([a7], [a8])
=========================

Hi Everyone.
I made this macro for fun, its based on a post last Sept.30.(Remove 1 from a
total after every day) answered by Ron Rosenfeld with a formula
(Networkdays).The post was on Answers.Microsoft.com
I do this for learning.I'm just at the learning stage.
I have a Date in A1 and a number in C1 that goes down by one everyday.
Now, my problem is, I want it just for Workdays..nor everydays.
I'm lost with this, can anyone help.

Option Explicit
Private Sub Workbook_Open()
Dim stdate As Date
Dim tday As Date
Dim ans As Integer
On Error GoTo Finish:
stdate = ActiveSheet.Range("A1")'Start Date
tday = Now()
ans = tday - stdate
If tday > stdate Then
Range("F1") = Range("C1") + 1 - ans
If Range("F1").Value <= 0 Then
Range("F1").Interior.ColorIndex = 3
MsgBox "Your Time has expired"
Else
Range("F1").Interior.ColorIndex = 0
End If
End If
Finish:
End Sub

Regards
Cimjet