Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identifying unique dates in a range of cells containing dates... | Excel Discussion (Misc queries) | |||
Identifying unique dates within a range of cells containing dates | Excel Discussion (Misc queries) | |||
need to convert list of dates to count no. of dates by week | Excel Worksheet Functions | |||
Calculating number of days between two dates that fall between two other dates | Excel Discussion (Misc queries) | |||
Charting data against dates where dates are not at fixed intervals | Charts and Charting in Excel |