A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Dates



 
 
Thread Tools Display Modes
  #1  
Old May 15th, 2007, 01:58 AM posted to microsoft.public.excel.misc
Scafidel
external usenet poster
 
Posts: 35
Default 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
  #2  
Old May 15th, 2007, 12:31 PM posted to microsoft.public.excel.misc
Joel
external usenet poster
 
Posts: 2,855
Default 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

  #3  
Old May 15th, 2007, 10:14 PM posted to microsoft.public.excel.misc
Scafidel
external usenet poster
 
Posts: 35
Default 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

  #4  
Old May 16th, 2007, 12:17 AM posted to microsoft.public.excel.misc
Joel
external usenet poster
 
Posts: 2,855
Default 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

  #5  
Old May 16th, 2007, 12:19 AM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 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
  #6  
Old May 16th, 2007, 12:22 AM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default 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
  #7  
Old May 16th, 2007, 01:22 AM posted to microsoft.public.excel.misc
Scafidel
external usenet poster
 
Posts: 35
Default 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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:09 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.