Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
weekly totals
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
weekly totals
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
weekly totals
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
weekly totals
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
weekly totals
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
weekly totals
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
weekly totals
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
weekly totals
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
weekly totals
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
weekly totals
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
weekly totals
Hi David,
I see. Then it ends on Fridays. Hernan "David" wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
weekly totals
The data:
Report for the month of february 08 day intakes exits dns 2/1/2008 3 0 1 2/2/2008 1 5 0 2/3/2008 3 0 1 2/4/2008 1 5 0 2/5/2008 3 0 1 2/6/2008 1 5 0 2/7/2008 3 0 1 2/8/2008 3 0 1 2/9/2008 1 5 0 2/10/2008 3 0 1 2/11/2008 3 0 1 2/12/2008 1 5 0 2/13/2008 3 0 1 2/14/2008 3 0 1 2/15/2008 1 5 0 2/16/2008 3 0 1 2/17/2008 3 0 1 2/18/2008 1 5 0 2/19/2008 3 0 1 2/20/2008 3 0 1 2/21/2008 1 5 0 2/22/2008 3 0 1 2/23/2008 3 0 1 2/24/2008 1 5 0 2/25/2008 3 0 1 2/26/2008 3 0 1 2/27/2008 1 5 0 2/28/2008 3 0 1 2/29/2008 3 0 1 The code: Sub Macro1() Range("A4").Select n = 1 Do Until ActiveCell.Value = "" ThisDay = Weekday(ActiveCell.Value) Select Case ThisDay Case 1 To 5 wkintake = wkintake + ActiveCell.Offset(0, 1).Value wkexits = wkexits + ActiveCell.Offset(0, 2).Value wkdns = wkdns + ActiveCell.Offset(0, 3).Value Case 6 wkintake = wkintake + ActiveCell.Offset(0, 1).Value wkexits = wkexits + ActiveCell.Offset(0, 2).Value wkdns = wkdns + ActiveCell.Offset(0, 3).Value If ThisDay = 6 Then Select Case n Case 1 wkintake1 = wkintake wkexits1 = wkexits wkdns1 = wkdns wkintake = 0 wkexits = 0 wkdns = 0 n = n + 1 Case 2 wkintake2 = wkintake wkexits2 = wkexits wkdns2 = wkdns wkintake = 0 wkexits = 0 wkdns = 0 n = n + 1 Case 3 wkintake3 = wkintake wkexits3 = wkexits wkdns3 = wkdns wkintake = 0 wkexits = 0 wkdns = 0 n = n + 1 Case 4 wkintake4 = wkintake wkexits4 = wkexits wkdns4 = wkdns wkintake = 0 wkexits = 0 wkdns = 0 n = n + 1 Case 5 wkintake5 = wkintake wkexits5 = wkexits wkdns5 = wkdns wkintake = 0 wkexits = 0 wkdns = 0 n = n + 1 End Select End If Case 7 wkintake = wkintake + ActiveCell.Offset(0, 1).Value wkexits = wkexits + ActiveCell.Offset(0, 2).Value wkdns = wkdns + ActiveCell.Offset(0, 3).Value Case Else Debug.Print "Check the date" End Select ActiveCell.Offset(1, 0).Select Loop End Sub The results are in these variables: wkintake1 wkexits1 wkdns1 wkintake2 wkexits2 wkdns2 Etc. 3 through 5 wkintake# Etc. 3 through 5 wkexits# Etc. 3 through 5 wkdns# Etc. 3 through 5 It is assumed you will have a Date under Day column A and it, the Data, will start on Row 4. It will fail if you have no data on the last day of the period (Friday.) Hope it helps. David "Hernan" wrote: Hi David, I see. Then it ends on Fridays. Hernan "David" wrote: 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
weekly totals
David,
Thank you so much for the code. However, I am trying to avoid to get a Date column under "day" and that's why I have day 1, day 2 etc... I was hoping the system's date and time could be read and parsed in order to know in what month you are at and then find the start of the month to determine what day of the week it is starting on... etc Maybe it would be easier to just ask for an input from the user when first starting the sheet, meaning the areas under intake, exit and dns are blank, for the month in course so day 1 can be calculated at the start of using the sheet? Hernan. "David" wrote: The data: Report for the month of february 08 day intakes exits dns 2/1/2008 3 0 1 2/2/2008 1 5 0 2/3/2008 3 0 1 2/4/2008 1 5 0 2/5/2008 3 0 1 2/6/2008 1 5 0 2/7/2008 3 0 1 2/8/2008 3 0 1 2/9/2008 1 5 0 2/10/2008 3 0 1 2/11/2008 3 0 1 2/12/2008 1 5 0 2/13/2008 3 0 1 2/14/2008 3 0 1 2/15/2008 1 5 0 2/16/2008 3 0 1 2/17/2008 3 0 1 2/18/2008 1 5 0 2/19/2008 3 0 1 2/20/2008 3 0 1 2/21/2008 1 5 0 2/22/2008 3 0 1 2/23/2008 3 0 1 2/24/2008 1 5 0 2/25/2008 3 0 1 2/26/2008 3 0 1 2/27/2008 1 5 0 2/28/2008 3 0 1 2/29/2008 3 0 1 The code: Sub Macro1() Range("A4").Select n = 1 Do Until ActiveCell.Value = "" ThisDay = Weekday(ActiveCell.Value) Select Case ThisDay Case 1 To 5 wkintake = wkintake + ActiveCell.Offset(0, 1).Value wkexits = wkexits + ActiveCell.Offset(0, 2).Value wkdns = wkdns + ActiveCell.Offset(0, 3).Value Case 6 wkintake = wkintake + ActiveCell.Offset(0, 1).Value wkexits = wkexits + ActiveCell.Offset(0, 2).Value wkdns = wkdns + ActiveCell.Offset(0, 3).Value If ThisDay = 6 Then Select Case n Case 1 wkintake1 = wkintake wkexits1 = wkexits wkdns1 = wkdns wkintake = 0 wkexits = 0 wkdns = 0 n = n + 1 Case 2 wkintake2 = wkintake wkexits2 = wkexits wkdns2 = wkdns wkintake = 0 wkexits = 0 wkdns = 0 n = n + 1 Case 3 wkintake3 = wkintake wkexits3 = wkexits wkdns3 = wkdns wkintake = 0 wkexits = 0 wkdns = 0 n = n + 1 Case 4 wkintake4 = wkintake wkexits4 = wkexits wkdns4 = wkdns wkintake = 0 wkexits = 0 wkdns = 0 n = n + 1 Case 5 wkintake5 = wkintake wkexits5 = wkexits wkdns5 = wkdns wkintake = 0 wkexits = 0 wkdns = 0 n = n + 1 End Select End If Case 7 wkintake = wkintake + ActiveCell.Offset(0, 1).Value wkexits = wkexits + ActiveCell.Offset(0, 2).Value wkdns = wkdns + ActiveCell.Offset(0, 3).Value Case Else Debug.Print "Check the date" End Select ActiveCell.Offset(1, 0).Select Loop End Sub The results are in these variables: wkintake1 wkexits1 wkdns1 wkintake2 wkexits2 wkdns2 Etc. 3 through 5 wkintake# Etc. 3 through 5 wkexits# Etc. 3 through 5 wkdns# Etc. 3 through 5 It is assumed you will have a Date under Day column A and it, the Data, will start on Row 4. It will fail if you have no data on the last day of the period (Friday.) Hope it helps. David "Hernan" wrote: Hi David, I see. Then it ends on Fridays. Hernan "David" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create weekly totals | Excel Worksheet Functions | |||
Weekly Totals | Excel Programming | |||
Weekly Totals on sheet | Excel Programming | |||
Summing Weekly Totals into Monthly Totals | Excel Worksheet Functions | |||
How can I subtotal my weekly totals by months? | Excel Worksheet Functions |