Formula using Days (DDD) in worksheet name
 

If I EVER get this finished!!
I have another request for my workbook to do. I have created a workbook that
creates workdays for the month and asks if the user wants to include
Saturdays and Sundays seperately. The workbook automatically excludes
holidays.
I have a total sales budget for the month, but now I need to put a different
sales goal in for Saturdays and Sundays, if they are used.
The approach I am using is formula based and I am weighting the different
days by the percentage the user can input on a setup page. For example, a
weekday is 100%, a Saturday can be 50% and a Sunday can be 33%. What I need
is to be able to COUNT the different days so I can get a total weight and
then apply the result to the daily sales goal (in the same cell on each
worksheet) for that particular day. The worksheets are named DDD-MMM-DD, so
would be MON-Sep-01, TUE-Sep-02, etc. I don't know how to have the formula
look at the DDD (Day) in the worksheet name and if Mon - Fri, apply the
weekday weight (just a range reference), Sat, the Sat weight and Sun, the Sun
weight.

This is the code that I am using to create the days of the month worksheets,
but I think this is a simply formula if I can look at the worksheet name for
the DDD part:

For iCtr = DateSerial(Year(myDate), Month(myDate), Day(myDate) + 1) To
DateSerial(Year(myDate), _
Month(myDate) + 1, 0)
'For iCtr = DateSerial(Year(myDate), Month(myDate), 1) To
DateSerial(Year(myDate), _
Month(myDate) + 1, 0) 'The 1 above starts with day one.
res = Application.Match(CLng(iCtr), Range("Holidays"), 0)
If IsError(res) Then
Select Case Weekday(iCtr)
Case Is = CaseSat '(Does all days, remove '& does only weekdays)
'do nothing
Case Is = CaseSun '(Does all days, remove ' & does only weekdays)
'do nothing
Case Else
Application.StatusBar = D
sh2.Copy after:=Sheets(Sheets.Count)
N = Sheets.Count - 5
ActiveSheet.Name = Format(iCtr, "ddd mm-dd")
Range("I4") = Format(iCtr, "mm-dd-yy")
Range("I10") = N + 1
ActiveSheet.Shapes("Button 1").Select
Selection.OnAction = "Mail_ActiveSheet"
Range("I3").Select
End Select
End If
Next

Formula using Days (DDD) in worksheet name
 

That's exactly where I'm heading...thanks for the lead! I'm actually taking
this
=MID(CELL("filename",B59),FIND("]",CELL("filename",B59))+1,256)
and then doing this
=LEFT(B59,3)
That gives me the name to compare, but I need one last thing...
As I create the sheets, I need a count of the weekdays, saturdays and
sundays that have been created. I'm looking at modifying and using something
like this, adding one for Saturday and one for Sunday as the weights could be
different:

Function GetWorkDays(StartDate As Long, EndDate As Long) As Long
' returns the count of days between StartDate - EndDate minus Saturdays and
Sundays
Dim d As Long, dCount As Long
For d = StartDate To EndDate
If WeekDay(d, vbMonday) < 6 Then
dCount = dCount + 1
End If
Next d
GetWorkDays = dCount
End Function

I've got to work on how to make sure the count matches the sheets that are
created, thinking maybe better to integrate the count into the code I already
have. Let me know if you have any thoughts on this and thanks so much for
getting back to me! I have to present both divisions tomorrow morning and I
can't do the 2nd one until I get all the coding done for the first one...and
I know I still have 8-10 hours work left. I really do appreciate your input!

"Tom Ogilvy" wrote:

Add code to put the name of the sheet (i.e. the date) in a cell on the sheet
and use that in your formula. Or easier, have it put a label in that cell:
Weekday, Sun, Sat

then have your formula evaluate based on that value.

--
Regards,
Tom Ogilvy


"David" wrote:

If I EVER get this finished!!
I have another request for my workbook to do. I have created a workbook that
creates workdays for the month and asks if the user wants to include
Saturdays and Sundays seperately. The workbook automatically excludes
holidays.
I have a total sales budget for the month, but now I need to put a different
sales goal in for Saturdays and Sundays, if they are used.
The approach I am using is formula based and I am weighting the different
days by the percentage the user can input on a setup page. For example, a
weekday is 100%, a Saturday can be 50% and a Sunday can be 33%. What I need
is to be able to COUNT the different days so I can get a total weight and
then apply the result to the daily sales goal (in the same cell on each
worksheet) for that particular day. The worksheets are named DDD-MMM-DD, so
would be MON-Sep-01, TUE-Sep-02, etc. I don't know how to have the formula
look at the DDD (Day) in the worksheet name and if Mon - Fri, apply the
weekday weight (just a range reference), Sat, the Sat weight and Sun, the Sun
weight.

This is the code that I am using to create the days of the month worksheets,
but I think this is a simply formula if I can look at the worksheet name for
the DDD part:

For iCtr = DateSerial(Year(myDate), Month(myDate), Day(myDate) + 1) To
DateSerial(Year(myDate), _
Month(myDate) + 1, 0)
'For iCtr = DateSerial(Year(myDate), Month(myDate), 1) To
DateSerial(Year(myDate), _
Month(myDate) + 1, 0) 'The 1 above starts with day one.
res = Application.Match(CLng(iCtr), Range("Holidays"), 0)
If IsError(res) Then
Select Case Weekday(iCtr)
Case Is = CaseSat '(Does all days, remove '& does only weekdays)
'do nothing
Case Is = CaseSun '(Does all days, remove ' & does only weekdays)
'do nothing
Case Else
Application.StatusBar = D
sh2.Copy after:=Sheets(Sheets.Count)
N = Sheets.Count - 5
ActiveSheet.Name = Format(iCtr, "ddd mm-dd")
Range("I4") = Format(iCtr, "mm-dd-yy")
Range("I10") = N + 1
ActiveSheet.Shapes("Button 1").Select
Selection.OnAction = "Mail_ActiveSheet"
Range("I3").Select
End Select
End If
Next

Formula using Days (DDD) in worksheet name
 

Tom,
I'm going to use CountIf...I just need to know how to use CountIF for all
worksheets in the workbook looking at the same cell. Seems I read somewhere
about ccell or csum. I'm putting the formula on sheet 4 and sheets 5 to up to
35 will be the days. Cell C59 will have the DDD abbreviation. Want to use
CountIF(C59,"SUN) but do it for all the sheets. That will be the
breakthrough. Any thoughts?

"Tom Ogilvy" wrote:

Add code to put the name of the sheet (i.e. the date) in a cell on the sheet
and use that in your formula. Or easier, have it put a label in that cell:
Weekday, Sun, Sat

then have your formula evaluate based on that value.

--
Regards,
Tom Ogilvy


"David" wrote:

If I EVER get this finished!!
I have another request for my workbook to do. I have created a workbook that
creates workdays for the month and asks if the user wants to include
Saturdays and Sundays seperately. The workbook automatically excludes
holidays.
I have a total sales budget for the month, but now I need to put a different
sales goal in for Saturdays and Sundays, if they are used.
The approach I am using is formula based and I am weighting the different
days by the percentage the user can input on a setup page. For example, a
weekday is 100%, a Saturday can be 50% and a Sunday can be 33%. What I need
is to be able to COUNT the different days so I can get a total weight and
then apply the result to the daily sales goal (in the same cell on each
worksheet) for that particular day. The worksheets are named DDD-MMM-DD, so
would be MON-Sep-01, TUE-Sep-02, etc. I don't know how to have the formula
look at the DDD (Day) in the worksheet name and if Mon - Fri, apply the
weekday weight (just a range reference), Sat, the Sat weight and Sun, the Sun
weight.

This is the code that I am using to create the days of the month worksheets,
but I think this is a simply formula if I can look at the worksheet name for
the DDD part:

For iCtr = DateSerial(Year(myDate), Month(myDate), Day(myDate) + 1) To
DateSerial(Year(myDate), _
Month(myDate) + 1, 0)
'For iCtr = DateSerial(Year(myDate), Month(myDate), 1) To
DateSerial(Year(myDate), _
Month(myDate) + 1, 0) 'The 1 above starts with day one.
res = Application.Match(CLng(iCtr), Range("Holidays"), 0)
If IsError(res) Then
Select Case Weekday(iCtr)
Case Is = CaseSat '(Does all days, remove '& does only weekdays)
'do nothing
Case Is = CaseSun '(Does all days, remove ' & does only weekdays)
'do nothing
Case Else
Application.StatusBar = D
sh2.Copy after:=Sheets(Sheets.Count)
N = Sheets.Count - 5
ActiveSheet.Name = Format(iCtr, "ddd mm-dd")
Range("I4") = Format(iCtr, "mm-dd-yy")
Range("I10") = N + 1
ActiveSheet.Shapes("Button 1").Select
Selection.OnAction = "Mail_ActiveSheet"
Range("I3").Select
End Select
End If
Next