Hello,

I have a sheet that looks like this:


A B C D
1 Report for the month of february 08
2
3 day intakes exits dns
4 1 3 0 1
5 2 1 5 0
.. .
.. .
.. .
totals 4 5 1

and so on to the 31st day. days 30th and 31st will have nothing for the
current report.

on the side I have:

F G H I
1 totals
2 intakes exits dns
3 week1
4 week2
5 week3
6 week4
7 week5

All of this is in a protected sheet

Is there a way to get the date from the system clock and from there
calculate the totals for intakes, exits and dns for each week. All I have to
do at the beginning of each month is to change the name of the month in
course and enter the individual number of intakes, exits and dns.

Thank you in advanced.


Hernan

In any given month, what constitutes "Week1"? Is it always the first 7 days
of the month (Day1 though Day7) or is it the physical calendar week (Sunday
through Saturday)? If the latter, do you have any rules about where in the
week the 1st of the month must occur?

Rick


"Hernan" wrote in message
...
Hello,

I have a sheet that looks like this:


A B C D
1 Report for the month of february 08
2
3 day intakes exits dns
4 1 3 0 1
5 2 1 5 0
. .
. .
. .
totals 4 5 1

and so on to the 31st day. days 30th and 31st will have nothing for the
current report.

on the side I have:

F G H I
1 totals
2 intakes exits dns
3 week1
4 week2
5 week3
6 week4
7 week5

All of this is in a protected sheet

Is there a way to get the date from the system clock and from there
calculate the totals for intakes, exits and dns for each week. All I have
to
do at the beginning of each month is to change the name of the month in
course and enter the individual number of intakes, exits and dns.

Thank you in advanced.


Hernan

I am guessing that each week ends on a certain day of the week ie. every
Friday and it seems that you can not do this with out designating each of
those days. Like Jan 2008, Fridays - 4, 11, 18 and 25, then you might come up
with If statements and use another column to designate a wk?.

"Rick Rothstein (MVP - VB)" wrote:

In any given month, what constitutes "Week1"? Is it always the first 7 days
of the month (Day1 though Day7) or is it the physical calendar week (Sunday
through Saturday)? If the latter, do you have any rules about where in the
week the 1st of the month must occur?

Rick


"Hernan" wrote in message
...
Hello,

I have a sheet that looks like this:


A B C D
1 Report for the month of february 08
2
3 day intakes exits dns
4 1 3 0 1
5 2 1 5 0
. .
. .
. .
totals 4 5 1

and so on to the 31st day. days 30th and 31st will have nothing for the
current report.

on the side I have:

F G H I
1 totals
2 intakes exits dns
3 week1
4 week2
5 week3
6 week4
7 week5

All of this is in a protected sheet

Is there a way to get the date from the system clock and from there
calculate the totals for intakes, exits and dns for each week. All I have
to
do at the beginning of each month is to change the name of the month in
course and enter the individual number of intakes, exits and dns.

Thank you in advanced.


Hernan

Thanks for replying and suggesting tha David.

Although, I am not even a newbie with VBA I will try to come up with at
least a pseudocode and see if I can accomplish something.


Hernan

"David" wrote:

I am guessing that each week ends on a certain day of the week ie. every
Friday and it seems that you can not do this with out designating each of
those days. Like Jan 2008, Fridays - 4, 11, 18 and 25, then you might come up
with If statements and use another column to designate a wk?.

"Rick Rothstein (MVP - VB)" wrote:

In any given month, what constitutes "Week1"? Is it always the first 7 days
of the month (Day1 though Day7) or is it the physical calendar week (Sunday
through Saturday)? If the latter, do you have any rules about where in the
week the 1st of the month must occur?

Rick


"Hernan" wrote in message
...
Hello,

I have a sheet that looks like this:


A B C D
1 Report for the month of february 08
2
3 day intakes exits dns
4 1 3 0 1
5 2 1 5 0
. .
. .
. .
totals 4 5 1

and so on to the 31st day. days 30th and 31st will have nothing for the
current report.

on the side I have:

F G H I
1 totals
2 intakes exits dns
3 week1
4 week2
5 week3
6 week4
7 week5

All of this is in a protected sheet

Is there a way to get the date from the system clock and from there
calculate the totals for intakes, exits and dns for each week. All I have
to
do at the beginning of each month is to change the name of the month in
course and enter the individual number of intakes, exits and dns.

Thank you in advanced.


Hernan

hi Again,

If you are using code, then it might be easier, but you still need to
designate the week ending day, be it Fri, Sat or Sunday. Then if you will
only have a months worth of data, you could "walk" down the dates, assuming
they are in order, and put data into a variable, adding each day, till you
reach the end day. Day of the week is fairly easy to find. Call the variables
wk1in, wk1exit, wk1dns, then wk2in, wk2exit, wk2dns, etc. The data for the
variable would be just offsets from the date you are on.

Then simply print the variable at the bottom of the data.


"David" wrote:

I am guessing that each week ends on a certain day of the week ie. every
Friday and it seems that you can not do this with out designating each of
those days. Like Jan 2008, Fridays - 4, 11, 18 and 25, then you might come up
with If statements and use another column to designate a wk?.

"Rick Rothstein (MVP - VB)" wrote:

In any given month, what constitutes "Week1"? Is it always the first 7 days
of the month (Day1 though Day7) or is it the physical calendar week (Sunday
through Saturday)? If the latter, do you have any rules about where in the
week the 1st of the month must occur?

Rick


"Hernan" wrote in message
...
Hello,

I have a sheet that looks like this:


A B C D
1 Report for the month of february 08
2
3 day intakes exits dns
4 1 3 0 1
5 2 1 5 0
. .
. .
. .
totals 4 5 1

and so on to the 31st day. days 30th and 31st will have nothing for the
current report.

on the side I have:

F G H I
1 totals
2 intakes exits dns
3 week1
4 week2
5 week3
6 week4
7 week5

All of this is in a protected sheet

Is there a way to get the date from the system clock and from there
calculate the totals for intakes, exits and dns for each week. All I have
to
do at the beginning of each month is to change the name of the month in
course and enter the individual number of intakes, exits and dns.

Thank you in advanced.


Hernan

Somehow I have the idea that the name of the month in course can be taken off
(parsed out) the system Date/Time, right? I do not know how to do it in VBA
though.

Hernan

"David" wrote:

hi Again,

If you are using code, then it might be easier, but you still need to
designate the week ending day, be it Fri, Sat or Sunday. Then if you will
only have a months worth of data, you could "walk" down the dates, assuming
they are in order, and put data into a variable, adding each day, till you
reach the end day. Day of the week is fairly easy to find. Call the variables
wk1in, wk1exit, wk1dns, then wk2in, wk2exit, wk2dns, etc. The data for the
variable would be just offsets from the date you are on.

Then simply print the variable at the bottom of the data.


"David" wrote:

I am guessing that each week ends on a certain day of the week ie. every
Friday and it seems that you can not do this with out designating each of
those days. Like Jan 2008, Fridays - 4, 11, 18 and 25, then you might come up
with If statements and use another column to designate a wk?.

"Rick Rothstein (MVP - VB)" wrote:

In any given month, what constitutes "Week1"? Is it always the first 7 days
of the month (Day1 though Day7) or is it the physical calendar week (Sunday
through Saturday)? If the latter, do you have any rules about where in the
week the 1st of the month must occur?

Rick


"Hernan" wrote in message
...
Hello,

I have a sheet that looks like this:


A B C D
1 Report for the month of february 08
2
3 day intakes exits dns
4 1 3 0 1
5 2 1 5 0
. .
. .
. .
totals 4 5 1

and so on to the 31st day. days 30th and 31st will have nothing for the
current report.

on the side I have:

F G H I
1 totals
2 intakes exits dns
3 week1
4 week2
5 week3
6 week4
7 week5

All of this is in a protected sheet

Is there a way to get the date from the system clock and from there
calculate the totals for intakes, exits and dns for each week. All I have
to
do at the beginning of each month is to change the name of the month in
course and enter the individual number of intakes, exits and dns.

Thank you in advanced.


Hernan

Hello Rick,

Thanks for replying.

That is why I have 5 weeks in it rpresented, because day one not always
start on a Monday or Sunday etc...

And no I don't have a rule for week 1 except that once the date has been
retrieved and the month determined, then day 1 can be pinpointed and acted
upon.

Sorry for the malrepresentation pf my sheet. Formatting goes down the tube,
even if it is ascii only? Well I am assuming I am writing in ascii... :/

TIA

"Rick Rothstein (MVP - VB)" wrote:

In any given month, what constitutes "Week1"? Is it always the first 7 days
of the month (Day1 though Day7) or is it the physical calendar week (Sunday
through Saturday)? If the latter, do you have any rules about where in the
week the 1st of the month must occur?

Rick


"Hernan" wrote in message
...
Hello,

I have a sheet that looks like this:


A B C D
1 Report for the month of february 08
2
3 day intakes exits dns
4 1 3 0 1
5 2 1 5 0
. .
. .
. .
totals 4 5 1

and so on to the 31st day. days 30th and 31st will have nothing for the
current report.

on the side I have:

F G H I
1 totals
2 intakes exits dns
3 week1
4 week2
5 week3
6 week4
7 week5

All of this is in a protected sheet

Is there a way to get the date from the system clock and from there
calculate the totals for intakes, exits and dns for each week. All I have
to
do at the beginning of each month is to change the name of the month in
course and enter the individual number of intakes, exits and dns.

Thank you in advanced.


Hernan

Then if, for example, Day 1 of the month were on a Thursday, your Week1
summation would contain only Day1, Day2 and, if weekends are included, Day3
(and, depending on whether the week is considered to start on Sunday or
Monday, possibly Day4), right? If so, clarify for us if weekends are counted
or not. If not, I guess Day1 could be the 2nd or 3rd day of the month if the
month starts on a Saturday or Sunday, right?

Rick


"Hernan" wrote in message
...
Hello Rick,

Thanks for replying.

That is why I have 5 weeks in it rpresented, because day one not always
start on a Monday or Sunday etc...

And no I don't have a rule for week 1 except that once the date has been
retrieved and the month determined, then day 1 can be pinpointed and acted
upon.

Sorry for the malrepresentation pf my sheet. Formatting goes down the
tube,
even if it is ascii only? Well I am assuming I am writing in ascii... :/

TIA

"Rick Rothstein (MVP - VB)" wrote:

In any given month, what constitutes "Week1"? Is it always the first 7
days
of the month (Day1 though Day7) or is it the physical calendar week
(Sunday
through Saturday)? If the latter, do you have any rules about where in
the
week the 1st of the month must occur?

Rick


"Hernan" wrote in message
...
Hello,

I have a sheet that looks like this:


A B C D
1 Report for the month of february 08
2
3 day intakes exits dns
4 1 3 0 1
5 2 1 5 0
. .
. .
. .
totals 4 5 1

and so on to the 31st day. days 30th and 31st will have nothing for the
current report.

on the side I have:

F G H I
1 totals
2 intakes exits dns
3 week1
4 week2
5 week3
6 week4
7 week5

All of this is in a protected sheet

Is there a way to get the date from the system clock and from there
calculate the totals for intakes, exits and dns for each week. All I
have
to
do at the beginning of each month is to change the name of the month in
course and enter the individual number of intakes, exits and dns.

Thank you in advanced.


Hernan

Hello Rick,

Yes, weekends are considered. So the 1st of the month could any day of the
week. That's why I have wek 1 thru 5, that as a matter of fact, it should be
6 weeks, for example the month in course. March 08.

"Rick Rothstein (MVP - VB)" wrote:

Then if, for example, Day 1 of the month were on a Thursday, your Week1
summation would contain only Day1, Day2 and, if weekends are included, Day3
(and, depending on whether the week is considered to start on Sunday or
Monday, possibly Day4), right? If so, clarify for us if weekends are counted
or not. If not, I guess Day1 could be the 2nd or 3rd day of the month if the
month starts on a Saturday or Sunday, right?

Rick


"Hernan" wrote in message
...
Hello Rick,

Thanks for replying.

That is why I have 5 weeks in it rpresented, because day one not always
start on a Monday or Sunday etc...

And no I don't have a rule for week 1 except that once the date has been
retrieved and the month determined, then day 1 can be pinpointed and acted
upon.

Sorry for the malrepresentation pf my sheet. Formatting goes down the
tube,
even if it is ascii only? Well I am assuming I am writing in ascii... :/

TIA

"Rick Rothstein (MVP - VB)" wrote:

In any given month, what constitutes "Week1"? Is it always the first 7
days
of the month (Day1 though Day7) or is it the physical calendar week
(Sunday
through Saturday)? If the latter, do you have any rules about where in
the
week the 1st of the month must occur?

Rick


"Hernan" wrote in message
...
Hello,

I have a sheet that looks like this:


A B C D
1 Report for the month of february 08
2
3 day intakes exits dns
4 1 3 0 1
5 2 1 5 0
. .
. .
. .
totals 4 5 1

and so on to the 31st day. days 30th and 31st will have nothing for the
current report.

on the side I have:

F G H I
1 totals
2 intakes exits dns
3 week1
4 week2
5 week3
6 week4
7 week5

All of this is in a protected sheet

Is there a way to get the date from the system clock and from there
calculate the totals for intakes, exits and dns for each week. All I
have
to
do at the beginning of each month is to change the name of the month in
course and enter the individual number of intakes, exits and dns.

Thank you in advanced.


Hernan

Hi Again,

I think the start of the week is not important. It is the last day of the
week that is important, it is the cut off date and could even stand alone. If
your week ends on Friday, Feb 2008, week1 could have one day only. If it ends
on Sat. then Feb 08, week2 has two days, if it end on Sun, then Feb 08, week
1 has 3 days.

So what day does your week end on?

David

"Hernan" wrote:

Hello Rick,

Yes, weekends are considered. So the 1st of the month could any day of the
week. That's why I have wek 1 thru 5, that as a matter of fact, it should be
6 weeks, for example the month in course. March 08.

"Rick Rothstein (MVP - VB)" wrote:

Then if, for example, Day 1 of the month were on a Thursday, your Week1
summation would contain only Day1, Day2 and, if weekends are included, Day3
(and, depending on whether the week is considered to start on Sunday or
Monday, possibly Day4), right? If so, clarify for us if weekends are counted
or not. If not, I guess Day1 could be the 2nd or 3rd day of the month if the
month starts on a Saturday or Sunday, right?

Rick


"Hernan" wrote in message
...
Hello Rick,

Thanks for replying.

That is why I have 5 weeks in it rpresented, because day one not always
start on a Monday or Sunday etc...

And no I don't have a rule for week 1 except that once the date has been
retrieved and the month determined, then day 1 can be pinpointed and acted
upon.

Sorry for the malrepresentation pf my sheet. Formatting goes down the
tube,
even if it is ascii only? Well I am assuming I am writing in ascii... :/

TIA

"Rick Rothstein (MVP - VB)" wrote:

In any given month, what constitutes "Week1"? Is it always the first 7
days
of the month (Day1 though Day7) or is it the physical calendar week
(Sunday
through Saturday)? If the latter, do you have any rules about where in
the
week the 1st of the month must occur?

Rick


"Hernan" wrote in message
...
Hello,

I have a sheet that looks like this:


A B C D
1 Report for the month of february 08
2
3 day intakes exits dns
4 1 3 0 1
5 2 1 5 0
. .
. .
. .
totals 4 5 1

and so on to the 31st day. days 30th and 31st will have nothing for the
current report.

on the side I have:

F G H I
1 totals
2 intakes exits dns
3 week1
4 week2
5 week3
6 week4
7 week5

All of this is in a protected sheet

Is there a way to get the date from the system clock and from there
calculate the totals for intakes, exits and dns for each week. All I
have
to
do at the beginning of each month is to change the name of the month in
course and enter the individual number of intakes, exits and dns.

Thank you in advanced.


Hernan