Dates
 

I have an invoice with a starting date through ending date of workdays. I
use NETWORKDAYS to find the total, but I would also like to list the dates
worked in one cell, like C1, below. similar to this. What formula should I
use?
A1 B1 C1
5/01/07 5/15/07 5/1,5/2,5/3,5/4,5/7,etc

Thanks
Scafidel
Louisiana

Dates
 

try this custom function
If A1 has 5/1/07 and b1 has 5/15
=daysworked(A1,B1)

or just
=daysworked(5/1/07,5/15/07)






Function daysworked(startdate as date, enddate as date) As String
Dim mydate As Date

daysworked = ""
For mydate = startdate To enddate

If (Weekday(mydate) = vbMonday) And _
(Weekday(mydate) <= vbFriday) Then

If daysworked < "" Then daysworked = daysworked + ","
a = Month(mydate)
b = Day(mydate)
daysworked = daysworked + Str(Month(mydate)) + "/" + Str(Day(mydate))

End If

Next mydate

End Function


"Scafidel" wrote:

I have an invoice with a starting date through ending date of workdays. I
use NETWORKDAYS to find the total, but I would also like to list the dates
worked in one cell, like C1, below. similar to this. What formula should I
use?
A1 B1 C1
5/01/07 5/15/07 5/1,5/2,5/3,5/4,5/7,etc

Thanks
Scafidel
Louisiana

Dates
 

Hey, thanks alot Joel. I guess I need to learn more about VB, I keep finding
solutions there. For some reason, I am getting a space after the slash, 5/
1, 5/ 2. Otherwise it works great.
Thanks
Scafidel
Louisiana

"Joel" wrote:

try this custom function
If A1 has 5/1/07 and b1 has 5/15
=daysworked(A1,B1)

or just
=daysworked(5/1/07,5/15/07)






Function daysworked(startdate as date, enddate as date) As String
Dim mydate As Date

daysworked = ""
For mydate = startdate To enddate

If (Weekday(mydate) = vbMonday) And _
(Weekday(mydate) <= vbFriday) Then

If daysworked < "" Then daysworked = daysworked + ","
a = Month(mydate)
b = Day(mydate)
daysworked = daysworked + Str(Month(mydate)) + "/" + Str(Day(mydate))

End If

Next mydate

End Function


"Scafidel" wrote:

I have an invoice with a starting date through ending date of workdays. I
use NETWORKDAYS to find the total, but I would also like to list the dates
worked in one cell, like C1, below. similar to this. What formula should I
use?
A1 B1 C1
5/01/07 5/15/07 5/1,5/2,5/3,5/4,5/7,etc

Thanks
Scafidel
Louisiana

Dates
 

the str function leave a space in front of the number for a sign. You can
eliminate the space like this

mid(Str(Day(mydate)),2) which skips the first blank character.

"Scafidel" wrote:

Hey, thanks alot Joel. I guess I need to learn more about VB, I keep finding
solutions there. For some reason, I am getting a space after the slash, 5/
1, 5/ 2. Otherwise it works great.
Thanks
Scafidel
Louisiana

"Joel" wrote:

try this custom function
If A1 has 5/1/07 and b1 has 5/15
=daysworked(A1,B1)

or just
=daysworked(5/1/07,5/15/07)






Function daysworked(startdate as date, enddate as date) As String
Dim mydate As Date

daysworked = ""
For mydate = startdate To enddate

If (Weekday(mydate) = vbMonday) And _
(Weekday(mydate) <= vbFriday) Then

If daysworked < "" Then daysworked = daysworked + ","
a = Month(mydate)
b = Day(mydate)
daysworked = daysworked + Str(Month(mydate)) + "/" + Str(Day(mydate))

End If

Next mydate

End Function


"Scafidel" wrote:

I have an invoice with a starting date through ending date of workdays. I
use NETWORKDAYS to find the total, but I would also like to list the dates
worked in one cell, like C1, below. similar to this. What formula should I
use?
A1 B1 C1
5/01/07 5/15/07 5/1,5/2,5/3,5/4,5/7,etc

Thanks
Scafidel
Louisiana

Dates
 

cStr() will add those space characters.

And it's more usual to use + to add numbers and & to concatenate text. (Excel's
VBA is very forgiving--but it may not always do what you want, so it's better to
use & when concatenating text.)

Option Explicit
Function DaysWorked(StartDate As Date, EndDate As Date) As String

Dim myDate As Date

DaysWorked = ""
For myDate = StartDate To EndDate
If (Weekday(myDate) = vbMonday) _
And (Weekday(myDate) <= vbFriday) Then
If DaysWorked < "" Then
DaysWorked = DaysWorked & ","
End If
DaysWorked = DaysWorked & Month(myDate) & "/" & Day(myDate)
End If
Next myDate

End Function



Joel wrote:

try this custom function
If A1 has 5/1/07 and b1 has 5/15
=daysworked(A1,B1)

or just
=daysworked(5/1/07,5/15/07)

Function daysworked(startdate as date, enddate as date) As String
Dim mydate As Date

daysworked = ""
For mydate = startdate To enddate

If (Weekday(mydate) = vbMonday) And _
(Weekday(mydate) <= vbFriday) Then

If daysworked < "" Then daysworked = daysworked + ","
a = Month(mydate)
b = Day(mydate)
daysworked = daysworked + Str(Month(mydate)) + "/" + Str(Day(mydate))

End If

Next mydate

End Function

"Scafidel" wrote:

I have an invoice with a starting date through ending date of workdays. I
use NETWORKDAYS to find the total, but I would also like to list the dates
worked in one cell, like C1, below. similar to this. What formula should I
use?
A1 B1 C1
5/01/07 5/15/07 5/1,5/2,5/3,5/4,5/7,etc

Thanks
Scafidel
Louisiana

--

Dave Peterson

Dates
 

Another version:

Option Explicit
Function DaysWorked(StartDate As Date, EndDate As Date) As String

Dim myDate As Date
Dim myStr As String

myStr = ""
For myDate = StartDate To EndDate
If (Weekday(myDate) = vbMonday) _
And (Weekday(myDate) <= vbFriday) Then
myStr = myStr & "," & Format(myDate, "m/d")
End If
Next myDate

If myStr < "" Then
myStr = Mid(myStr, 2)
End If

DaysWorked = myStr

End Function




Scafidel wrote:

Hey, thanks alot Joel. I guess I need to learn more about VB, I keep finding
solutions there. For some reason, I am getting a space after the slash, 5/
1, 5/ 2. Otherwise it works great.
Thanks
Scafidel
Louisiana

"Joel" wrote:

try this custom function
If A1 has 5/1/07 and b1 has 5/15
=daysworked(A1,B1)

or just
=daysworked(5/1/07,5/15/07)






Function daysworked(startdate as date, enddate as date) As String
Dim mydate As Date

daysworked = ""
For mydate = startdate To enddate

If (Weekday(mydate) = vbMonday) And _
(Weekday(mydate) <= vbFriday) Then

If daysworked < "" Then daysworked = daysworked + ","
a = Month(mydate)
b = Day(mydate)
daysworked = daysworked + Str(Month(mydate)) + "/" + Str(Day(mydate))

End If

Next mydate

End Function


"Scafidel" wrote:

I have an invoice with a starting date through ending date of workdays. I
use NETWORKDAYS to find the total, but I would also like to list the dates
worked in one cell, like C1, below. similar to this. What formula should I
use?
A1 B1 C1
5/01/07 5/15/07 5/1,5/2,5/3,5/4,5/7,etc

Thanks
Scafidel
Louisiana

--

Dave Peterson

Dates
 

Thanks for all the good avise, guys!
I really appreciate it.

"Dave Peterson" wrote:

cStr() will add those space characters.

And it's more usual to use + to add numbers and & to concatenate text. (Excel's
VBA is very forgiving--but it may not always do what you want, so it's better to
use & when concatenating text.)

Option Explicit
Function DaysWorked(StartDate As Date, EndDate As Date) As String

Dim myDate As Date

DaysWorked = ""
For myDate = StartDate To EndDate
If (Weekday(myDate) = vbMonday) _
And (Weekday(myDate) <= vbFriday) Then
If DaysWorked < "" Then
DaysWorked = DaysWorked & ","
End If
DaysWorked = DaysWorked & Month(myDate) & "/" & Day(myDate)
End If
Next myDate

End Function



Joel wrote:

try this custom function
If A1 has 5/1/07 and b1 has 5/15
=daysworked(A1,B1)

or just
=daysworked(5/1/07,5/15/07)

Function daysworked(startdate as date, enddate as date) As String
Dim mydate As Date

daysworked = ""
For mydate = startdate To enddate

If (Weekday(mydate) = vbMonday) And _
(Weekday(mydate) <= vbFriday) Then

If daysworked < "" Then daysworked = daysworked + ","
a = Month(mydate)
b = Day(mydate)
daysworked = daysworked + Str(Month(mydate)) + "/" + Str(Day(mydate))

End If

Next mydate

End Function

"Scafidel" wrote:

I have an invoice with a starting date through ending date of workdays. I
use NETWORKDAYS to find the total, but I would also like to list the dates
worked in one cell, like C1, below. similar to this. What formula should I
use?
A1 B1 C1
5/01/07 5/15/07 5/1,5/2,5/3,5/4,5/7,etc

Thanks
Scafidel
Louisiana

--

Dave Peterson