Spreading date range across days in weeks (advanced)
Hello
This particular problem has been causing me some consternation, and I was
wondering if anyone has already solved this problem or has a creative
solution
I have a date range, and I would like to convert that into how many days in
each week correspond to that range.
Example
Consider a range of an employee’s working dates. Lets say I have two input
fields: Start date (A3) and end date (B3), and want to be able to count the
number of days in each week C3: F3. Consider March of 2010, so Week 1
commences on 01/03/2010, Week 2 commences on 08/03/2010, etc. The starting
date of each week is in C1:F1
A3 and B3 are inputs, the formula is required for C3:F3. They work weekends,
so ignore the working day effect.
A B C D
E F
1 01/03/2010
08/03/2010 15/03/2010 22/03/2010
2 Start Date End Date Week1 Week2
Week3 Week4
3 05/03/2010 17/03/2010 3 {days in week) 7 {days in week} 3
{days in week} 0 {days in week}
As you can see, in this input range there is 3 days in Week 1 (5th to 7th
inc.) and 7 days in Week 2(8th to 14th), 3 days in
Please, no macros. Thanks in advance
|
0 |
|
Utf
2/19/2010 9:07:01 AM
excel.misc
78881 articles. 
5 followers.
7 Replies
1138 Views
Wow, that didn't display so well. Here's an image of the worksheet
http://i50.tinypic.com/2s79myr.jpg
"StephenT" wrote:
> Hello
>
> This particular problem has been causing me some consternation, and I was
> wondering if anyone has already solved this problem or has a creative
> solution
>
>
> I have a date range, and I would like to convert that into how many days in
> each week correspond to that range.
>
>
> Example
> Consider a range of an employee’s working dates. Lets say I have two input
> fields: Start date (A3) and end date (B3), and want to be able to count the
> number of days in each week C3: F3. Consider March of 2010, so Week 1
> commences on 01/03/2010, Week 2 commences on 08/03/2010, etc. The starting
> date of each week is in C1:F1
>
> A3 and B3 are inputs, the formula is required for C3:F3. They work weekends,
> so ignore the working day effect.
>
>
> A B C D
> E F
>
> 1 01/03/2010
> 08/03/2010 15/03/2010 22/03/2010
> 2 Start Date End Date Week1 Week2
> Week3 Week4
> 3 05/03/2010 17/03/2010 3 {days in week) 7 {days in week} 3
> {days in week} 0 {days in week}
>
> As you can see, in this input range there is 3 days in Week 1 (5th to 7th
> inc.) and 7 days in Week 2(8th to 14th), 3 days in
>
> Please, no macros. Thanks in advance
>
|
|
0 |
|
Utf
2/19/2010 9:14:01 AM
Based on your example, I have this but you need to try other dates to see if
it is robust enough to use.
Cell C3 =DAYS360(IF($A$3>$C$1,$A$3,$C$1)-1,IF($B$3>D1,D1-1,$B$3))
Cell D3 =DAYS360(IF($A$3>$C$1,$A$3,$C$1)-1,IF($B$3>E1,E1-1,$B$3))-C3
Cell E3 =DAYS360(IF($A$3>$C$1,$A$3,$C$1)-1,IF($B$3>F1,F1-1,$B$3))-D3-C3
Cell F3 =DAYS360(A3-1,B3)-SUM(C3:E3)
"StephenT" wrote:
> Wow, that didn't display so well. Here's an image of the worksheet
> http://i50.tinypic.com/2s79myr.jpg
>
> "StephenT" wrote:
>
> > Hello
> >
> > This particular problem has been causing me some consternation, and I was
> > wondering if anyone has already solved this problem or has a creative
> > solution
> >
> >
> > I have a date range, and I would like to convert that into how many days in
> > each week correspond to that range.
> >
> >
> > Example
> > Consider a range of an employee’s working dates. Lets say I have two input
> > fields: Start date (A3) and end date (B3), and want to be able to count the
> > number of days in each week C3: F3. Consider March of 2010, so Week 1
> > commences on 01/03/2010, Week 2 commences on 08/03/2010, etc. The starting
> > date of each week is in C1:F1
> >
> > A3 and B3 are inputs, the formula is required for C3:F3. They work weekends,
> > so ignore the working day effect.
> >
> >
> > A B C D
> > E F
> >
> > 1 01/03/2010
> > 08/03/2010 15/03/2010 22/03/2010
> > 2 Start Date End Date Week1 Week2
> > Week3 Week4
> > 3 05/03/2010 17/03/2010 3 {days in week) 7 {days in week} 3
> > {days in week} 0 {days in week}
> >
> > As you can see, in this input range there is 3 days in Week 1 (5th to 7th
> > inc.) and 7 days in Week 2(8th to 14th), 3 days in
> >
> > Please, no macros. Thanks in advance
> >
|
|
0 |
|
Utf
2/19/2010 9:38:01 AM
Thanks :), but unfortunately this doesn't work if the start date is not in
the first week. This logic may be modified but I fear we will end up where I
have been the last 24 hours and wrestling with a heinous nested IF
statement...
Any other suggestions?
":)" wrote:
> Based on your example, I have this but you need to try other dates to see if
> it is robust enough to use.
>
> Cell C3 =DAYS360(IF($A$3>$C$1,$A$3,$C$1)-1,IF($B$3>D1,D1-1,$B$3))
> Cell D3 =DAYS360(IF($A$3>$C$1,$A$3,$C$1)-1,IF($B$3>E1,E1-1,$B$3))-C3
> Cell E3 =DAYS360(IF($A$3>$C$1,$A$3,$C$1)-1,IF($B$3>F1,F1-1,$B$3))-D3-C3
> Cell F3 =DAYS360(A3-1,B3)-SUM(C3:E3)
>
> "StephenT" wrote:
>
> > Wow, that didn't display so well. Here's an image of the worksheet
> > http://i50.tinypic.com/2s79myr.jpg
> >
> > "StephenT" wrote:
> >
> > > Hello
> > >
> > > This particular problem has been causing me some consternation, and I was
> > > wondering if anyone has already solved this problem or has a creative
> > > solution
> > >
> > >
> > > I have a date range, and I would like to convert that into how many days in
> > > each week correspond to that range.
> > >
> > >
> > > Example
> > > Consider a range of an employee’s working dates. Lets say I have two input
> > > fields: Start date (A3) and end date (B3), and want to be able to count the
> > > number of days in each week C3: F3. Consider March of 2010, so Week 1
> > > commences on 01/03/2010, Week 2 commences on 08/03/2010, etc. The starting
> > > date of each week is in C1:F1
> > >
> > > A3 and B3 are inputs, the formula is required for C3:F3. They work weekends,
> > > so ignore the working day effect.
> > >
> > >
> > > A B C D
> > > E F
> > >
> > > 1 01/03/2010
> > > 08/03/2010 15/03/2010 22/03/2010
> > > 2 Start Date End Date Week1 Week2
> > > Week3 Week4
> > > 3 05/03/2010 17/03/2010 3 {days in week) 7 {days in week} 3
> > > {days in week} 0 {days in week}
> > >
> > > As you can see, in this input range there is 3 days in Week 1 (5th to 7th
> > > inc.) and 7 days in Week 2(8th to 14th), 3 days in
> > >
> > > Please, no macros. Thanks in advance
> > >
|
|
0 |
|
Utf
2/19/2010 9:59:02 AM
Anyone? Don't tell me I've stumped the famed Excel discussion group...
This'll be a first.
"StephenT" wrote:
> Thanks :), but unfortunately this doesn't work if the start date is not in
> the first week. This logic may be modified but I fear we will end up where I
> have been the last 24 hours and wrestling with a heinous nested IF
> statement...
>
> Any other suggestions?
>
> ":)" wrote:
>
> > Based on your example, I have this but you need to try other dates to see if
> > it is robust enough to use.
> >
> > Cell C3 =DAYS360(IF($A$3>$C$1,$A$3,$C$1)-1,IF($B$3>D1,D1-1,$B$3))
> > Cell D3 =DAYS360(IF($A$3>$C$1,$A$3,$C$1)-1,IF($B$3>E1,E1-1,$B$3))-C3
> > Cell E3 =DAYS360(IF($A$3>$C$1,$A$3,$C$1)-1,IF($B$3>F1,F1-1,$B$3))-D3-C3
> > Cell F3 =DAYS360(A3-1,B3)-SUM(C3:E3)
> >
> > "StephenT" wrote:
> >
> > > Wow, that didn't display so well. Here's an image of the worksheet
> > > http://i50.tinypic.com/2s79myr.jpg
> > >
> > > "StephenT" wrote:
> > >
> > > > Hello
> > > >
> > > > This particular problem has been causing me some consternation, and I was
> > > > wondering if anyone has already solved this problem or has a creative
> > > > solution
> > > >
> > > >
> > > > I have a date range, and I would like to convert that into how many days in
> > > > each week correspond to that range.
> > > >
> > > >
> > > > Example
> > > > Consider a range of an employee’s working dates. Lets say I have two input
> > > > fields: Start date (A3) and end date (B3), and want to be able to count the
> > > > number of days in each week C3: F3. Consider March of 2010, so Week 1
> > > > commences on 01/03/2010, Week 2 commences on 08/03/2010, etc. The starting
> > > > date of each week is in C1:F1
> > > >
> > > > A3 and B3 are inputs, the formula is required for C3:F3. They work weekends,
> > > > so ignore the working day effect.
> > > >
> > > >
> > > > A B C D
> > > > E F
> > > >
> > > > 1 01/03/2010
> > > > 08/03/2010 15/03/2010 22/03/2010
> > > > 2 Start Date End Date Week1 Week2
> > > > Week3 Week4
> > > > 3 05/03/2010 17/03/2010 3 {days in week) 7 {days in week} 3
> > > > {days in week} 0 {days in week}
> > > >
> > > > As you can see, in this input range there is 3 days in Week 1 (5th to 7th
> > > > inc.) and 7 days in Week 2(8th to 14th), 3 days in
> > > >
> > > > Please, no macros. Thanks in advance
> > > >
|
|
0 |
|
Utf
2/19/2010 1:20:05 PM
You might want to wait a day or two for people to see your problem. This =
is a newsgoup that people from around the globe read.
StephenT wrote:
> Anyone? Don't tell me I've stumped the famed Excel discussion group... =
> This'll be a first.
>=20
> "StephenT" wrote:
>=20
>=20
>>Thanks :), but unfortunately this doesn't work if the start date is not=
in=20
>>the first week. This logic may be modified but I fear we will end up wh=
ere I=20
>>have been the last 24 hours and wrestling with a heinous nested IF=20
>>statement...=20
>>
>>Any other suggestions?=20
>>
>>":)" wrote:
>>
>>
>>>Based on your example, I have this but you need to try other dates to =
see if=20
>>>it is robust enough to use.
>>>
>>>Cell C3 =3DDAYS360(IF($A$3>$C$1,$A$3,$C$1)-1,IF($B$3>D1,D1-1,$B$3))
>>>Cell D3 =3DDAYS360(IF($A$3>$C$1,$A$3,$C$1)-1,IF($B$3>E1,E1-1,$B$3))-C3=
>>>Cell E3 =3DDAYS360(IF($A$3>$C$1,$A$3,$C$1)-1,IF($B$3>F1,F1-1,$B$3))-D3=
-C3
>>>Cell F3 =3DDAYS360(A3-1,B3)-SUM(C3:E3)
>>>
>>>"StephenT" wrote:
>>>
>>>
>>>>Wow, that didn't display so well. Here's an image of the worksheet=20
>>>>http://i50.tinypic.com/2s79myr.jpg
>>>>
>>>>"StephenT" wrote:
>>>>
>>>>
>>>>>Hello=20
>>>>>
>>>>>This particular problem has been causing me some consternation, and =
I was=20
>>>>>wondering if anyone has already solved this problem or has a creativ=
e=20
>>>>>solution=20
>>>>>
>>>>>
>>>>>I have a date range, and I would like to convert that into how many =
days in=20
>>>>>each week correspond to that range.=20
>>>>>
>>>>>
>>>>>Example=20
>>>>>Consider a range of an employee=E2=80=99s working dates. Lets say I =
have two input=20
>>>>>fields: Start date (A3) and end date (B3), and want to be able to co=
unt the=20
>>>>>number of days in each week C3: F3. Consider March of 2010, so Week =
1=20
>>>>>commences on 01/03/2010, Week 2 commences on 08/03/2010, etc. The st=
arting=20
>>>>>date of each week is in C1:F1
>>>>>
>>>>>A3 and B3 are inputs, the formula is required for C3:F3. They work w=
eekends,=20
>>>>>so ignore the working day effect.
>>>>>
>>>>>
>>>>> A B C =
D =20
>>>>> E F =
=20
>>>>> =20
>>>>>1 01/03/2010 =20
>>>>>08/03/2010 15/03/2010 22/03/2010
>>>>>2 Start Date End Date Week1 Week2 =
=20
>>>>> Week3 Week4 =20
>>>>>3 05/03/2010 17/03/2010 3 {days in week) 7 {days in week=
} 3=20
>>>>>{days in week} 0 {days in week} =20
>>>>>
>>>>>As you can see, in this input range there is 3 days in Week 1 (5th t=
o 7th=20
>>>>>inc.) and 7 days in Week 2(8th to 14th), 3 days in=20
>>>>>
>>>>>Please, no macros. Thanks in advance
>>>>>
|
|
0 |
|
Bob
2/19/2010 2:11:21 PM
Try this:
=SUMPRODUCT(--(WEEKNUM(ROW(INDIRECT($A3&":"&$B3)),2)=WEEKNUM(C$1,2)))
"StephenT" wrote:
> Hello
>
> This particular problem has been causing me some consternation, and I was
> wondering if anyone has already solved this problem or has a creative
> solution
>
>
> I have a date range, and I would like to convert that into how many days in
> each week correspond to that range.
>
>
> Example
> Consider a range of an employee’s working dates. Lets say I have two input
> fields: Start date (A3) and end date (B3), and want to be able to count the
> number of days in each week C3: F3. Consider March of 2010, so Week 1
> commences on 01/03/2010, Week 2 commences on 08/03/2010, etc. The starting
> date of each week is in C1:F1
>
> A3 and B3 are inputs, the formula is required for C3:F3. They work weekends,
> so ignore the working day effect.
>
>
> A B C D
> E F
>
> 1 01/03/2010
> 08/03/2010 15/03/2010 22/03/2010
> 2 Start Date End Date Week1 Week2
> Week3 Week4
> 3 05/03/2010 17/03/2010 3 {days in week) 7 {days in week} 3
> {days in week} 0 {days in week}
>
> As you can see, in this input range there is 3 days in Week 1 (5th to 7th
> inc.) and 7 days in Week 2(8th to 14th), 3 days in
>
> Please, no macros. Thanks in advance
>
|
|
0 |
|
Utf
2/19/2010 3:24:01 PM
Try this:
=MAX(0,MIN(7-MAX(0,$A3-C$1),$B3-MAX(C$1,$A3)+1))
and copy to the other cells.
Regards,
Fred
PS. Never use DAYS360 unless you really want 30 days in every month.
"StephenT" <StephenT@discussions.microsoft.com> wrote in message
news:8CC824FF-F47B-4C9D-84C3-1255E5A3F6FD@microsoft.com...
> Anyone? Don't tell me I've stumped the famed Excel discussion group...
> This'll be a first.
>
> "StephenT" wrote:
>
>> Thanks :), but unfortunately this doesn't work if the start date is not
>> in
>> the first week. This logic may be modified but I fear we will end up
>> where I
>> have been the last 24 hours and wrestling with a heinous nested IF
>> statement...
>>
>> Any other suggestions?
>>
>> ":)" wrote:
>>
>> > Based on your example, I have this but you need to try other dates to
>> > see if
>> > it is robust enough to use.
>> >
>> > Cell C3 =DAYS360(IF($A$3>$C$1,$A$3,$C$1)-1,IF($B$3>D1,D1-1,$B$3))
>> > Cell D3 =DAYS360(IF($A$3>$C$1,$A$3,$C$1)-1,IF($B$3>E1,E1-1,$B$3))-C3
>> > Cell E3 =DAYS360(IF($A$3>$C$1,$A$3,$C$1)-1,IF($B$3>F1,F1-1,$B$3))-D3-C3
>> > Cell F3 =DAYS360(A3-1,B3)-SUM(C3:E3)
>> >
>> > "StephenT" wrote:
>> >
>> > > Wow, that didn't display so well. Here's an image of the worksheet
>> > > http://i50.tinypic.com/2s79myr.jpg
>> > >
>> > > "StephenT" wrote:
>> > >
>> > > > Hello
>> > > >
>> > > > This particular problem has been causing me some consternation, and
>> > > > I was
>> > > > wondering if anyone has already solved this problem or has a
>> > > > creative
>> > > > solution
>> > > >
>> > > >
>> > > > I have a date range, and I would like to convert that into how many
>> > > > days in
>> > > > each week correspond to that range.
>> > > >
>> > > >
>> > > > Example
>> > > > Consider a range of an employee’s working dates. Lets say I have
>> > > > two input
>> > > > fields: Start date (A3) and end date (B3), and want to be able to
>> > > > count the
>> > > > number of days in each week C3: F3. Consider March of 2010, so Week
>> > > > 1
>> > > > commences on 01/03/2010, Week 2 commences on 08/03/2010, etc. The
>> > > > starting
>> > > > date of each week is in C1:F1
>> > > >
>> > > > A3 and B3 are inputs, the formula is required for C3:F3. They work
>> > > > weekends,
>> > > > so ignore the working day effect.
>> > > >
>> > > >
>> > > > A B C
>> > > > D
>> > > > E F
>> > > >
>> > > > 1 01/03/2010
>> > > > 08/03/2010 15/03/2010 22/03/2010
>> > > > 2 Start Date End Date Week1 Week2
>> > > > Week3 Week4
>> > > > 3 05/03/2010 17/03/2010 3 {days in week) 7 {days in
>> > > > week} 3
>> > > > {days in week} 0 {days in week}
>> > > >
>> > > > As you can see, in this input range there is 3 days in Week 1 (5th
>> > > > to 7th
>> > > > inc.) and 7 days in Week 2(8th to 14th), 3 days in
>> > > >
>> > > > Please, no macros. Thanks in advance
>> > > >
|
|
0 |
|
Fred
2/19/2010 3:28:58 PM
Similar Artilces:
reports without year to date
Hi, At the end of the night we generate a report to show the sales reps daily sales. Is there a way to gererate this report without showing the stores month and year to date sales? I are running RMS 2.0 Thanks, Melissa What kind of report are you using currently? The "Sales -> Top Performers -> Top Sales Reps" report can be used with no filter on how much they made to create a report of all sales reps totals, if that is what you're looking for. -Chris "melcort" wrote: > Hi, At the end of the night we generate a report to show the sales reps daily &...
calculating periods (yrs, months and days)
Hi I need a formula to calculate the date 4.78 yrs back from 01/08/09. i manually did which is 20/10/04. any help 01/08/09 being in A1: =A1-4.78*365-INT(4.78/4) Regards, Stefi „Rohit” ezt írta: > Hi > > I need a formula to calculate the date 4.78 yrs back from 01/08/09. i > manually did which is 20/10/04. any help ...
Named Ranges #6
On the menu I go to Insert > Name > Define, and then the box pops u with a list of all my named ranges. I notice that in the "main list area" that some of the range names hav another name in the column to the right. I am going to try to attach picture of what I'm talking about that shows a cluster of range name that have the word "estimate" in the column to the right, yet other don't have any name in the column to the right. What does it mean when there is or isn't a name to the right of th Range Name? Thanks! Dave +-------------------------------...
Delete contents of cells in a range based on value of a cell
I want to be able to delete the contents of a range of cells (A100:K200) based on the values I manually enter into cells A1 and B1, everything to run in Sheet1. A1's entry is the first row of the range to delete (A100:K100) and B1's entry is the last row of the range (A200:K200). Can someone provide a fairly simple macro for this? Thanks in advance. Michael To deleted entire rows, assuming by your example that A1 would be 100 and B1 would be 200. Sub dele() Dim x As Long, y As Long x = Range("A1").Value y = Range("B1").Value Rows(x &...
Activity Date update Case Follow-up
I am trying to figure out a way for the Activity Due Date to update the Follow-up date of the case when the activity is created. Any Ideas? What I origianlly wanted to do was to create a workflow rule to change the status of the case 24 hours before the service activity occurs. I could not find a way to relate serrvice activities adn cases in workflow. So I will settle for a solution to my initial question. Thanks You would need to implement some custom logic via an assembly in workflow on the service activity to obtain this functionality. -- Graham This posting is provided "...
Printing Ranges
I am wanting to print a range from a spreadsheet that will be different each time a print is req'd. How do I set my range prior to printing? I have tried using - Range("A1", Range("A1").Offset(6, 19).End(xlDown)).Select Range1 = ActiveCell PrintArea = Range1 ActiveSheet.SelectedSheets.PrintOut Copies:=1 and variations of above, but cannot seem to nail it down. Think I have the bones of it in there, but not sure what else is required. Many thanks in advance people!! Hello Mikey, Try Dim myRange As String Range("A1", Range("A1").Of...
Hide data points prior to a certain date
I have a line chart with three series over time. One series represents actual values. For future dates, I made a formula return "#N/A" so that they won't be plotted. For one of the other lines, I would like to do something similar for *past* dates up to March 2008. When I change the source data to start at the March 2008 value, of course this skews the graph and puts the March 2008 value where the graph starts, at November 2007. I can't do the #N/A trick, since other data depends on these values. I would be happy to just right-click and hide the November through March data ...
Tricky Scenario finding days/months/years!
Thanks for the advice. how does one go about sending you an excel document? Regard -- Rizits ----------------------------------------------------------------------- Rizitsu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1584 View this thread: http://www.excelforum.com/showthread.php?threadid=31482 ...
Invoice
Hi, Is there any fast easy way to know how many days old an invoice is? I know Smartlists has aging in buckets like 1-30 - 31-60 etc... and Aged Trail Balances and Historical Aged Trial Balance will tell you within which bucket it falls but what about a way for me to know: 1. How many days old an invoice is. For example if the invoice date is 1/1/2010, for the date of this posting (1/24/2010) it would read 24 days. 2. How many days past due an invoice is. For the same example above and the terms were Prepayment or COD etc. it would read 24 days over due. I would like to k...
Specific Date Headers
I have a report that is sorted by date. I have formatted the dates with a "ww" format that groups and creates a header by the particular week number. For every group or week number I would like to create a header that states the week number and the date of the monday of that week. Example: Week 2 Data1 Data2 1/8/08 Data1 Data2 1/9/08 Should be: Week 2 Beginning Monday 1/7/08 Data1 Data2 1/8/08 Data1 Data2 1/9/08 Any thoughts, ideas, or suggestions would be appreciated. If the question is unclear, please let me kno...
calculate report by date from two tables
I am a novice on Access. I have a table that inputs a date and a daily market value for different stocks/shares I have a second table that inputs purchases and sales each day of the different stocks/shares - balances may not change every day but there may be more than one transaction in a day I want a report that calculates the current market value of the current holding of stocks/shares I have worked out how to produce a report with a running total of the stocks/shares but want the report to "look up" the date and report back what that days market price was so that I can do a ...
Last Date 05-17-07
I am setting up and inventory DB I have a parent table (Material) and a child table (Material Count) The child table has a field (Date Entered) What I need to do is to be able to extract the record in the child table by the last date entered, This will allow me to construct a report that list all of the records in the parent table and the last date inventory was taken on that item Thanks for your help ...
Email Sent Dates are incorrect
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop Hi. <br><br>I just installed Entourage 2008. Everything seems to be working properly except that the 'Sent' dates for some people are way in the future. These people are using Lotus Notes for mail. Some of the Notes users' dates come in fine, it seems to be just a couple of people. The Sent dates come in as X/X/2018. <br><br>I use "Sent" as my sort for mail. I tried to use "Received" but since I am not constantly conn...
Format date 12-30-09
Hello I'm working on excel 2007 and I have a workbook with 15 sheets. The problem is that the dates are all in English US and I want them in English UK. What is the solution to change all the 15 sheets at once? Thanks in advance. Group the sheets, then format cells. Then ungroup the sheets to avoid confusion. -- David Biddulph MAX wrote: > Hello > I'm working on excel 2007 and I have a workbook with 15 sheets. The > problem is that the dates are all in English US and I want them in > English UK. What is the solution to change all the 15 sheets at once? ...
mySql insert statement using date
Hi All, Having a syntax error when using the update statement to a linked table. Getting a 3155 error in mySQL. Any help is appreciated. vSQL = "insert into audit_history (auditor_id, audit_date, audit_shift_code, associate_id, wave, dept_id, error_date, error_shift_code, task_error_id, qty_aud, qty_err, comments, dtimecre, dtimemod, usrmod)" & _ "select [cbAudAuditor], [txAudDate], [cbAudShift], [cbAudEmp], [txAudWave], [cbAudDept], [txAudErrDate], [cbAudErrShift], [cbAudErrID], [txAudQty], [txAudErrQty], [txAudDesc], [txCreModDate], [txCreModDate...
spread sheet #2
how do i chart ticket sales by ticket number & ticket seller using excel 2000 It helps to arrange your data first. Put the X values or labels in a column and the corresponding Y values in the next column or columns. Put a label identifying each Y column at the top of the data, but leave the cell above the X data clear. You can start anywhere, not necessarily cell A1, but avoid skipping rows and columns in the data. Then select the data and start the chart wizard. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTe...
Contracts Advanced Find Excel Export
When I perform an Advanced Find on "Contracts", the Export to Excel icon is not available when the search results are displayed. Is there a way to export Contract Advanced Find Results? Thanks Not sure why Microsoft didn't put the export to Excel function in the Contract Advanced Find view, but if you click File > Print, you will get a print window where you can select all and copy/paste into Excel and get pretty much the same results. -- Matt Wittemann http://icu-mscrm.blogspot.com "Rocco P" wrote: > When I perform an Advanced Find on "Contracts&q...
AR Summary Lifetime to date figures
On the AR Summary screen there are fieldsa for length of time to pay on the Credit Summary page. There is a value for year to date and lifetime to date. Does anyone know exactly how the the lifetime to date is calculated? Is it calculated everytime a customer pays their bill or is it calculated once a year when the AR year-end closing procedure is done? It would help if you gave the exact window name because there are a few summary windows. But, the calculation of the Average Days to Pay value, which I presume is the field value to which you are referring, is described in KB849914. ...
Printing a Date Range for a Week Number
Hi All- I have a table listing Week Numbers. For instance, this week of the year is Week 25. What I would like to do is print the Date Range for the specific Week Number. i.e.; "Week 25 is June 18 - June 24, 2007". I hope that makes sense. Thanks for any help you can provide. Fred On Jun 22, 8:41 pm, "FredK" <fred.kel...@ci.tracy.ca.us> wrote: > Hi All- > > I have a table listing Week Numbers. For instance, this week of the year is > Week 25. > > What I would like to do is print the Date Range for the specific Week > Number. i.e.; ...
Custom formatting numbers & dates with a template (for receipts/invoices)
I have a template that I use to make receipts. One of the cells holds the information that I would like to change every time I open the template. For instance, the first order in Jan. 2004 looks like this: 1-J04 The second order in Jan. 2004 looks like this: 2-J04 First order, Feb. 04: 1-F04 And so on. I can't seem to figure out how to do this & would really appreciate help! TIA. -Jude Public Sub Auto_Open() Dim iPos As Long Dim iVal As Long With Worksheets("Sheet1").Range("A1") If Right(.Value, 3) = Format(Date, "mmm") Then iPos ...
Named Cell Ranges #3
I have a number of workbooks that look at a variables workbook which has a number of named cell ranges. I have added new lines to a particular named area and now some of the formulae in the other workbooks don't work. I'm assuming it's to do with the change in the named range. I'm hoping someone can tell me it's easy to fix! Thanks Hi we need your formula, your name definition, the expected result, etc :-) -- Regards Frank Kabel Frankfurt, Germany "Blackcat" <Blackcat@discussions.microsoft.com> schrieb im Newsbeitrag news:087D8C4D-75C6-4449-98AF-96...
Question about Table Date Time Stamps
Hi, I have a question about the date time stamp for the tables. I am looking at the "Table" tab under the "Objects" in the main view in access. There is a "Created" and "Modified" date. My question is when is created and modified updated with a new date. I ran a query that deleted table "XYZ" and then appended 3 tables to "XYZ" but the "Modified" date did not change. Does this make sense. Thanks for your help on this! Those dates are intended to reflect when design changes are made to the table (adding or remov...
Date
When I restarted my computer on January 2, 2004, the date in MSMoney had changed to June 2, 2004. Why did this happen and how do I fix it? Money doesn't make this stuff up. Check the system data again. "Cindy" <anonymous@discussions.microsoft.com> wrote in message news:033b01c3d174$8e57dc00$a301280a@phx.gbl... > When I restarted my computer on January 2, 2004, the date > in MSMoney had changed to June 2, 2004. Why did this > happen and how do I fix it? In microsoft.public.money, Cindy wrote: >When I restarted my computer on January 2, 2004, the date >...
I want to calculate a date 1 year ahead of a date in a bookmark
I want to be able to calculate a new date 1 year ahead of a date that is stored or entered in a bookmark so that if the value under the bookm ark changes the calculated date value will also change. In Excel I can easily do date calculation but if it's available in Word help is NO help. Terry, See if something like this would work. Sub DateReCalc() Dim strOldDate As String Dim dteNewDate As Date strOldDate = ActiveDocument.Bookmarks("origDate").Range.Text dteNewDate = DateAdd("yyyy", 1, DateValue(strOldDate)) MsgBox dteNewDate End Sub Steve Y...
How to stop text changing to date
I have a column of text that's in the style 1994/95, 1995/96, etc. When I paste it into Excel, after setting the column to Format Cell General, the entries beginning with 19 go in as text. That's what's supposed to happen. But -- here's my problem -- the entries 2000/01, 2001/02, etc. go in as dates. I can't fix this even by manually deleting the dates, resetting the cell format to General, and TYPING in the characters. The cells above are still showing as Format Cell General. The problem cells mysteriously change from General to Custom. There must be some way to ...
Hi, At the end of the night we generate a report to show the sales reps daily sales. Is there a way to gererate this report without showing the stores month and year to date sales? I are running RMS 2.0 Thanks, Melissa What kind of report are you using currently? The "Sales -> Top Performers -> Top Sales Reps" report can be used with no filter on how much they made to create a report of all sales reps totals, if that is what you're looking for. -Chris "melcort" wrote: > Hi, At the end of the night we generate a report to show the sales reps daily &...
calculating periods (yrs, months and days)
Hi I need a formula to calculate the date 4.78 yrs back from 01/08/09. i manually did which is 20/10/04. any help 01/08/09 being in A1: =A1-4.78*365-INT(4.78/4) Regards, Stefi „Rohit” ezt írta: > Hi > > I need a formula to calculate the date 4.78 yrs back from 01/08/09. i > manually did which is 20/10/04. any help ...
Named Ranges #6
On the menu I go to Insert > Name > Define, and then the box pops u with a list of all my named ranges. I notice that in the "main list area" that some of the range names hav another name in the column to the right. I am going to try to attach picture of what I'm talking about that shows a cluster of range name that have the word "estimate" in the column to the right, yet other don't have any name in the column to the right. What does it mean when there is or isn't a name to the right of th Range Name? Thanks! Dave +-------------------------------...
Delete contents of cells in a range based on value of a cell
I want to be able to delete the contents of a range of cells (A100:K200) based on the values I manually enter into cells A1 and B1, everything to run in Sheet1. A1's entry is the first row of the range to delete (A100:K100) and B1's entry is the last row of the range (A200:K200). Can someone provide a fairly simple macro for this? Thanks in advance. Michael To deleted entire rows, assuming by your example that A1 would be 100 and B1 would be 200. Sub dele() Dim x As Long, y As Long x = Range("A1").Value y = Range("B1").Value Rows(x &...
Activity Date update Case Follow-up
I am trying to figure out a way for the Activity Due Date to update the Follow-up date of the case when the activity is created. Any Ideas? What I origianlly wanted to do was to create a workflow rule to change the status of the case 24 hours before the service activity occurs. I could not find a way to relate serrvice activities adn cases in workflow. So I will settle for a solution to my initial question. Thanks You would need to implement some custom logic via an assembly in workflow on the service activity to obtain this functionality. -- Graham This posting is provided "...
Printing Ranges
I am wanting to print a range from a spreadsheet that will be different each time a print is req'd. How do I set my range prior to printing? I have tried using - Range("A1", Range("A1").Offset(6, 19).End(xlDown)).Select Range1 = ActiveCell PrintArea = Range1 ActiveSheet.SelectedSheets.PrintOut Copies:=1 and variations of above, but cannot seem to nail it down. Think I have the bones of it in there, but not sure what else is required. Many thanks in advance people!! Hello Mikey, Try Dim myRange As String Range("A1", Range("A1").Of...
Hide data points prior to a certain date
I have a line chart with three series over time. One series represents actual values. For future dates, I made a formula return "#N/A" so that they won't be plotted. For one of the other lines, I would like to do something similar for *past* dates up to March 2008. When I change the source data to start at the March 2008 value, of course this skews the graph and puts the March 2008 value where the graph starts, at November 2007. I can't do the #N/A trick, since other data depends on these values. I would be happy to just right-click and hide the November through March data ...
Tricky Scenario finding days/months/years!
Thanks for the advice. how does one go about sending you an excel document? Regard -- Rizits ----------------------------------------------------------------------- Rizitsu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1584 View this thread: http://www.excelforum.com/showthread.php?threadid=31482 ...
Invoice
Hi, Is there any fast easy way to know how many days old an invoice is? I know Smartlists has aging in buckets like 1-30 - 31-60 etc... and Aged Trail Balances and Historical Aged Trial Balance will tell you within which bucket it falls but what about a way for me to know: 1. How many days old an invoice is. For example if the invoice date is 1/1/2010, for the date of this posting (1/24/2010) it would read 24 days. 2. How many days past due an invoice is. For the same example above and the terms were Prepayment or COD etc. it would read 24 days over due. I would like to k...
Specific Date Headers
I have a report that is sorted by date. I have formatted the dates with a "ww" format that groups and creates a header by the particular week number. For every group or week number I would like to create a header that states the week number and the date of the monday of that week. Example: Week 2 Data1 Data2 1/8/08 Data1 Data2 1/9/08 Should be: Week 2 Beginning Monday 1/7/08 Data1 Data2 1/8/08 Data1 Data2 1/9/08 Any thoughts, ideas, or suggestions would be appreciated. If the question is unclear, please let me kno...
calculate report by date from two tables
I am a novice on Access. I have a table that inputs a date and a daily market value for different stocks/shares I have a second table that inputs purchases and sales each day of the different stocks/shares - balances may not change every day but there may be more than one transaction in a day I want a report that calculates the current market value of the current holding of stocks/shares I have worked out how to produce a report with a running total of the stocks/shares but want the report to "look up" the date and report back what that days market price was so that I can do a ...
Last Date 05-17-07
I am setting up and inventory DB I have a parent table (Material) and a child table (Material Count) The child table has a field (Date Entered) What I need to do is to be able to extract the record in the child table by the last date entered, This will allow me to construct a report that list all of the records in the parent table and the last date inventory was taken on that item Thanks for your help ...
Email Sent Dates are incorrect
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Email Client: pop Hi. <br><br>I just installed Entourage 2008. Everything seems to be working properly except that the 'Sent' dates for some people are way in the future. These people are using Lotus Notes for mail. Some of the Notes users' dates come in fine, it seems to be just a couple of people. The Sent dates come in as X/X/2018. <br><br>I use "Sent" as my sort for mail. I tried to use "Received" but since I am not constantly conn...
Format date 12-30-09
Hello I'm working on excel 2007 and I have a workbook with 15 sheets. The problem is that the dates are all in English US and I want them in English UK. What is the solution to change all the 15 sheets at once? Thanks in advance. Group the sheets, then format cells. Then ungroup the sheets to avoid confusion. -- David Biddulph MAX wrote: > Hello > I'm working on excel 2007 and I have a workbook with 15 sheets. The > problem is that the dates are all in English US and I want them in > English UK. What is the solution to change all the 15 sheets at once? ...
mySql insert statement using date
Hi All, Having a syntax error when using the update statement to a linked table. Getting a 3155 error in mySQL. Any help is appreciated. vSQL = "insert into audit_history (auditor_id, audit_date, audit_shift_code, associate_id, wave, dept_id, error_date, error_shift_code, task_error_id, qty_aud, qty_err, comments, dtimecre, dtimemod, usrmod)" & _ "select [cbAudAuditor], [txAudDate], [cbAudShift], [cbAudEmp], [txAudWave], [cbAudDept], [txAudErrDate], [cbAudErrShift], [cbAudErrID], [txAudQty], [txAudErrQty], [txAudDesc], [txCreModDate], [txCreModDate...
spread sheet #2
how do i chart ticket sales by ticket number & ticket seller using excel 2000 It helps to arrange your data first. Put the X values or labels in a column and the corresponding Y values in the next column or columns. Put a label identifying each Y column at the top of the data, but leave the cell above the X data clear. You can start anywhere, not necessarily cell A1, but avoid skipping rows and columns in the data. Then select the data and start the chart wizard. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTe...
Contracts Advanced Find Excel Export
When I perform an Advanced Find on "Contracts", the Export to Excel icon is not available when the search results are displayed. Is there a way to export Contract Advanced Find Results? Thanks Not sure why Microsoft didn't put the export to Excel function in the Contract Advanced Find view, but if you click File > Print, you will get a print window where you can select all and copy/paste into Excel and get pretty much the same results. -- Matt Wittemann http://icu-mscrm.blogspot.com "Rocco P" wrote: > When I perform an Advanced Find on "Contracts&q...
AR Summary Lifetime to date figures
On the AR Summary screen there are fieldsa for length of time to pay on the Credit Summary page. There is a value for year to date and lifetime to date. Does anyone know exactly how the the lifetime to date is calculated? Is it calculated everytime a customer pays their bill or is it calculated once a year when the AR year-end closing procedure is done? It would help if you gave the exact window name because there are a few summary windows. But, the calculation of the Average Days to Pay value, which I presume is the field value to which you are referring, is described in KB849914. ...
Printing a Date Range for a Week Number
Hi All- I have a table listing Week Numbers. For instance, this week of the year is Week 25. What I would like to do is print the Date Range for the specific Week Number. i.e.; "Week 25 is June 18 - June 24, 2007". I hope that makes sense. Thanks for any help you can provide. Fred On Jun 22, 8:41 pm, "FredK" <fred.kel...@ci.tracy.ca.us> wrote: > Hi All- > > I have a table listing Week Numbers. For instance, this week of the year is > Week 25. > > What I would like to do is print the Date Range for the specific Week > Number. i.e.; ...
Custom formatting numbers & dates with a template (for receipts/invoices)
I have a template that I use to make receipts. One of the cells holds the information that I would like to change every time I open the template. For instance, the first order in Jan. 2004 looks like this: 1-J04 The second order in Jan. 2004 looks like this: 2-J04 First order, Feb. 04: 1-F04 And so on. I can't seem to figure out how to do this & would really appreciate help! TIA. -Jude Public Sub Auto_Open() Dim iPos As Long Dim iVal As Long With Worksheets("Sheet1").Range("A1") If Right(.Value, 3) = Format(Date, "mmm") Then iPos ...
Named Cell Ranges #3
I have a number of workbooks that look at a variables workbook which has a number of named cell ranges. I have added new lines to a particular named area and now some of the formulae in the other workbooks don't work. I'm assuming it's to do with the change in the named range. I'm hoping someone can tell me it's easy to fix! Thanks Hi we need your formula, your name definition, the expected result, etc :-) -- Regards Frank Kabel Frankfurt, Germany "Blackcat" <Blackcat@discussions.microsoft.com> schrieb im Newsbeitrag news:087D8C4D-75C6-4449-98AF-96...
Question about Table Date Time Stamps
Hi, I have a question about the date time stamp for the tables. I am looking at the "Table" tab under the "Objects" in the main view in access. There is a "Created" and "Modified" date. My question is when is created and modified updated with a new date. I ran a query that deleted table "XYZ" and then appended 3 tables to "XYZ" but the "Modified" date did not change. Does this make sense. Thanks for your help on this! Those dates are intended to reflect when design changes are made to the table (adding or remov...
Date
When I restarted my computer on January 2, 2004, the date in MSMoney had changed to June 2, 2004. Why did this happen and how do I fix it? Money doesn't make this stuff up. Check the system data again. "Cindy" <anonymous@discussions.microsoft.com> wrote in message news:033b01c3d174$8e57dc00$a301280a@phx.gbl... > When I restarted my computer on January 2, 2004, the date > in MSMoney had changed to June 2, 2004. Why did this > happen and how do I fix it? In microsoft.public.money, Cindy wrote: >When I restarted my computer on January 2, 2004, the date >...
I want to calculate a date 1 year ahead of a date in a bookmark
I want to be able to calculate a new date 1 year ahead of a date that is stored or entered in a bookmark so that if the value under the bookm ark changes the calculated date value will also change. In Excel I can easily do date calculation but if it's available in Word help is NO help. Terry, See if something like this would work. Sub DateReCalc() Dim strOldDate As String Dim dteNewDate As Date strOldDate = ActiveDocument.Bookmarks("origDate").Range.Text dteNewDate = DateAdd("yyyy", 1, DateValue(strOldDate)) MsgBox dteNewDate End Sub Steve Y...
How to stop text changing to date
I have a column of text that's in the style 1994/95, 1995/96, etc. When I paste it into Excel, after setting the column to Format Cell General, the entries beginning with 19 go in as text. That's what's supposed to happen. But -- here's my problem -- the entries 2000/01, 2001/02, etc. go in as dates. I can't fix this even by manually deleting the dates, resetting the cell format to General, and TYPING in the characters. The cells above are still showing as Format Cell General. The problem cells mysteriously change from General to Custom. There must be some way to ...