| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date calculation for Monday of one month to the Monday of the next
Morning from a cold SA, sunny but temp is +- 10 deg C
Need to show the dates from the Monday of one month to the Monday of the next month. This is for a payroll period, thus need to show the dates in between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that users can capture the respective hours per employee. Column B would have the respective dates as per the formula, cell B1 would have the start date which the data capturer inputs, cell B4 would be the first Monday of June and then cell B5 would have the Tuesday and then so on until we reach the 2nd July which is the first monday in the new month, thereafter the cells should not show any date. The employees names will be in row 4 Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculation for Monday of one month to the Monday of the next
Look he
http://www.cpearson.com/excel/DateTimeWS.htm#NthDoWYear -- Kind regards, Niek Otten Microsoft MVP - Excel "Sunnyskies" wrote in message ... | Morning from a cold SA, sunny but temp is +- 10 deg C | | Need to show the dates from the Monday of one month to the Monday of the | next month. This is for a payroll period, thus need to show the dates in | between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that | users can capture the respective hours per employee. | | Column B would have the respective dates as per the formula, cell B1 would | have the start date which the data capturer inputs, cell B4 would be the | first Monday of June and then cell B5 would have the Tuesday and then so on | until we reach the 2nd July which is the first monday in the new month, | thereafter the cells should not show any date. | | The employees names will be in row 4 | | Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculation for Monday of one month to the Monday of the next
Hi I'm not sure what level of automation you want in this but the simplest
way to do it is have your person put the date (must be 1st of the month) in B1 and then this formula in B4 which will calculate the first Monday of the date in B1 =IF(WEEKDAY(B1,3)0,7-WEEKDAY(B1,3),0)+B1 In B5 you then simply enter =B4+1 and drag down as far as you require. Mike "Sunnyskies" wrote: Morning from a cold SA, sunny but temp is +- 10 deg C Need to show the dates from the Monday of one month to the Monday of the next month. This is for a payroll period, thus need to show the dates in between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that users can capture the respective hours per employee. Column B would have the respective dates as per the formula, cell B1 would have the start date which the data capturer inputs, cell B4 would be the first Monday of June and then cell B5 would have the Tuesday and then so on until we reach the 2nd July which is the first monday in the new month, thereafter the cells should not show any date. The employees names will be in row 4 Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculation for Monday of one month to the Monday of the next
I made a search with keywords "first Monday" and got several answers. Try it!
Regards, Stefi €˛Sunnyskies€¯ ezt Ć*rta: Morning from a cold SA, sunny but temp is +- 10 deg C Need to show the dates from the Monday of one month to the Monday of the next month. This is for a payroll period, thus need to show the dates in between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that users can capture the respective hours per employee. Column B would have the respective dates as per the formula, cell B1 would have the start date which the data capturer inputs, cell B4 would be the first Monday of June and then cell B5 would have the Tuesday and then so on until we reach the 2nd July which is the first monday in the new month, thereafter the cells should not show any date. The employees names will be in row 4 Thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculation for Monday of one month to the Monday of the next
Try:
B1=Start of month e.g 01/06/07 B4: =$B$1+(8-WEEKDAY($B$1,2)) B5: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+(8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),B4+1, ""),"") Copy down until blank cell reached HTH "Sunnyskies" wrote: Morning from a cold SA, sunny but temp is +- 10 deg C Need to show the dates from the Monday of one month to the Monday of the next month. This is for a payroll period, thus need to show the dates in between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that users can capture the respective hours per employee. Column B would have the respective dates as per the formula, cell B1 would have the start date which the data capturer inputs, cell B4 would be the first Monday of June and then cell B5 would have the Tuesday and then so on until we reach the 2nd July which is the first monday in the new month, thereafter the cells should not show any date. The employees names will be in row 4 Thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculation for Monday of one month to the Monday of the
Almost but how does it stop at the first Monday of the next month?
"Mike H" wrote: Hi I'm not sure what level of automation you want in this but the simplest way to do it is have your person put the date (must be 1st of the month) in B1 and then this formula in B4 which will calculate the first Monday of the date in B1 =IF(WEEKDAY(B1,3)0,7-WEEKDAY(B1,3),0)+B1 In B5 you then simply enter =B4+1 and drag down as far as you require. Mike "Sunnyskies" wrote: Morning from a cold SA, sunny but temp is +- 10 deg C Need to show the dates from the Monday of one month to the Monday of the next month. This is for a payroll period, thus need to show the dates in between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that users can capture the respective hours per employee. Column B would have the respective dates as per the formula, cell B1 would have the start date which the data capturer inputs, cell B4 would be the first Monday of June and then cell B5 would have the Tuesday and then so on until we reach the 2nd July which is the first monday in the new month, thereafter the cells should not show any date. The employees names will be in row 4 Thanks |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculation for Monday of one month to the Monday of the
Excellent.
Because you nailed it and the formula looks exhausting, by the powers invested by me from me, I suggest you go home early today. Cheers "Toppers" wrote: Try: B1=Start of month e.g 01/06/07 B4: =$B$1+(8-WEEKDAY($B$1,2)) B5: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+(8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),B4+1, ""),"") Copy down until blank cell reached HTH "Sunnyskies" wrote: Morning from a cold SA, sunny but temp is +- 10 deg C Need to show the dates from the Monday of one month to the Monday of the next month. This is for a payroll period, thus need to show the dates in between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that users can capture the respective hours per employee. Column B would have the respective dates as per the formula, cell B1 would have the start date which the data capturer inputs, cell B4 would be the first Monday of June and then cell B5 would have the Tuesday and then so on until we reach the 2nd July which is the first monday in the new month, thereafter the cells should not show any date. The employees names will be in row 4 Thanks |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculation for Monday of one month to the Monday of the
Stop, before you go home.
There is a problem with September: 03 September 2007 Monday 04 September 2007 Tuesday 05 September 2007 Wednesday 06 September 2007 Thursday 07 September 2007 Friday 08 September 2007 Saturday 09 September 2007 Sunday 10 September 2007 Monday 11 September 2007 Tuesday 12 September 2007 Wednesday 13 September 2007 Thursday 14 September 2007 Friday 15 September 2007 Saturday 16 September 2007 Sunday 17 September 2007 Monday 18 September 2007 Tuesday 19 September 2007 Wednesday 20 September 2007 Thursday 21 September 2007 Friday 22 September 2007 Saturday 23 September 2007 Sunday 24 September 2007 Monday 25 September 2007 Tuesday 26 September 2007 Wednesday 27 September 2007 Thursday 28 September 2007 Friday 29 September 2007 Saturday 30 September 2007 Sunday 01 October 2007 Monday 02 October 2007 Tuesday 03 October 2007 Wednesday 04 October 2007 Thursday 05 October 2007 Friday 06 October 2007 Saturday 07 October 2007 Sunday 08 October 2007 Monday It should have stopped at the 1st October. Thanks "Toppers" wrote: Try: B1=Start of month e.g 01/06/07 B4: =$B$1+(8-WEEKDAY($B$1,2)) B5: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+(8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),B4+1, ""),"") Copy down until blank cell reached HTH "Sunnyskies" wrote: Morning from a cold SA, sunny but temp is +- 10 deg C Need to show the dates from the Monday of one month to the Monday of the next month. This is for a payroll period, thus need to show the dates in between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that users can capture the respective hours per employee. Column B would have the respective dates as per the formula, cell B1 would have the start date which the data capturer inputs, cell B4 would be the first Monday of June and then cell B5 would have the Tuesday and then so on until we reach the 2nd July which is the first monday in the new month, thereafter the cells should not show any date. The employees names will be in row 4 Thanks |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculation for Monday of one month to the Monday of the
try:
=IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+MOD((8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),7),B4 +1,""),"") "Sunnyskies" wrote: Stop, before you go home. There is a problem with September: 03 September 2007 Monday 04 September 2007 Tuesday 05 September 2007 Wednesday 06 September 2007 Thursday 07 September 2007 Friday 08 September 2007 Saturday 09 September 2007 Sunday 10 September 2007 Monday 11 September 2007 Tuesday 12 September 2007 Wednesday 13 September 2007 Thursday 14 September 2007 Friday 15 September 2007 Saturday 16 September 2007 Sunday 17 September 2007 Monday 18 September 2007 Tuesday 19 September 2007 Wednesday 20 September 2007 Thursday 21 September 2007 Friday 22 September 2007 Saturday 23 September 2007 Sunday 24 September 2007 Monday 25 September 2007 Tuesday 26 September 2007 Wednesday 27 September 2007 Thursday 28 September 2007 Friday 29 September 2007 Saturday 30 September 2007 Sunday 01 October 2007 Monday 02 October 2007 Tuesday 03 October 2007 Wednesday 04 October 2007 Thursday 05 October 2007 Friday 06 October 2007 Saturday 07 October 2007 Sunday 08 October 2007 Monday It should have stopped at the 1st October. Thanks "Toppers" wrote: Try: B1=Start of month e.g 01/06/07 B4: =$B$1+(8-WEEKDAY($B$1,2)) B5: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+(8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),B4+1, ""),"") Copy down until blank cell reached HTH "Sunnyskies" wrote: Morning from a cold SA, sunny but temp is +- 10 deg C Need to show the dates from the Monday of one month to the Monday of the next month. This is for a payroll period, thus need to show the dates in between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that users can capture the respective hours per employee. Column B would have the respective dates as per the formula, cell B1 would have the start date which the data capturer inputs, cell B4 would be the first Monday of June and then cell B5 would have the Tuesday and then so on until we reach the 2nd July which is the first monday in the new month, thereafter the cells should not show any date. The employees names will be in row 4 Thanks |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculation for Monday of one month to the Monday of the
or ...
=IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+(7-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),3)),B4+1, ""),"") Removes need for MOD "Toppers" wrote: try: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+MOD((8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),7),B4 +1,""),"") "Sunnyskies" wrote: Stop, before you go home. There is a problem with September: 03 September 2007 Monday 04 September 2007 Tuesday 05 September 2007 Wednesday 06 September 2007 Thursday 07 September 2007 Friday 08 September 2007 Saturday 09 September 2007 Sunday 10 September 2007 Monday 11 September 2007 Tuesday 12 September 2007 Wednesday 13 September 2007 Thursday 14 September 2007 Friday 15 September 2007 Saturday 16 September 2007 Sunday 17 September 2007 Monday 18 September 2007 Tuesday 19 September 2007 Wednesday 20 September 2007 Thursday 21 September 2007 Friday 22 September 2007 Saturday 23 September 2007 Sunday 24 September 2007 Monday 25 September 2007 Tuesday 26 September 2007 Wednesday 27 September 2007 Thursday 28 September 2007 Friday 29 September 2007 Saturday 30 September 2007 Sunday 01 October 2007 Monday 02 October 2007 Tuesday 03 October 2007 Wednesday 04 October 2007 Thursday 05 October 2007 Friday 06 October 2007 Saturday 07 October 2007 Sunday 08 October 2007 Monday It should have stopped at the 1st October. Thanks "Toppers" wrote: Try: B1=Start of month e.g 01/06/07 B4: =$B$1+(8-WEEKDAY($B$1,2)) B5: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+(8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),B4+1, ""),"") Copy down until blank cell reached HTH "Sunnyskies" wrote: Morning from a cold SA, sunny but temp is +- 10 deg C Need to show the dates from the Monday of one month to the Monday of the next month. This is for a payroll period, thus need to show the dates in between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that users can capture the respective hours per employee. Column B would have the respective dates as per the formula, cell B1 would have the start date which the data capturer inputs, cell B4 would be the first Monday of June and then cell B5 would have the Tuesday and then so on until we reach the 2nd July which is the first monday in the new month, thereafter the cells should not show any date. The employees names will be in row 4 Thanks |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculation for Monday of one month to the Monday of the
Oops another problem, this time with October. The start date even though
entered as 2007/10/01 in B1 comes through as 08 October 2007 in cell B4 08 October 2007 Monday 09 October 2007 Tuesday 10 October 2007 Wednesday 11 October 2007 Thursday 12 October 2007 Friday 13 October 2007 Saturday 14 October 2007 Sunday 15 October 2007 Monday 16 October 2007 Tuesday 17 October 2007 Wednesday 18 October 2007 Thursday 19 October 2007 Friday 20 October 2007 Saturday 21 October 2007 Sunday 22 October 2007 Monday 23 October 2007 Tuesday 24 October 2007 Wednesday 25 October 2007 Thursday 26 October 2007 Friday 27 October 2007 Saturday 28 October 2007 Sunday 29 October 2007 Monday 30 October 2007 Tuesday 31 October 2007 Wednesday 01 November 2007 Thursday 02 November 2007 Friday 03 November 2007 Saturday 04 November 2007 Sunday 05 November 2007 Monday "Toppers" wrote: try: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+MOD((8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),7),B4 +1,""),"") "Sunnyskies" wrote: Stop, before you go home. There is a problem with September: 03 September 2007 Monday 04 September 2007 Tuesday 05 September 2007 Wednesday 06 September 2007 Thursday 07 September 2007 Friday 08 September 2007 Saturday 09 September 2007 Sunday 10 September 2007 Monday 11 September 2007 Tuesday 12 September 2007 Wednesday 13 September 2007 Thursday 14 September 2007 Friday 15 September 2007 Saturday 16 September 2007 Sunday 17 September 2007 Monday 18 September 2007 Tuesday 19 September 2007 Wednesday 20 September 2007 Thursday 21 September 2007 Friday 22 September 2007 Saturday 23 September 2007 Sunday 24 September 2007 Monday 25 September 2007 Tuesday 26 September 2007 Wednesday 27 September 2007 Thursday 28 September 2007 Friday 29 September 2007 Saturday 30 September 2007 Sunday 01 October 2007 Monday 02 October 2007 Tuesday 03 October 2007 Wednesday 04 October 2007 Thursday 05 October 2007 Friday 06 October 2007 Saturday 07 October 2007 Sunday 08 October 2007 Monday It should have stopped at the 1st October. Thanks "Toppers" wrote: Try: B1=Start of month e.g 01/06/07 B4: =$B$1+(8-WEEKDAY($B$1,2)) B5: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+(8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),B4+1, ""),"") Copy down until blank cell reached HTH "Sunnyskies" wrote: Morning from a cold SA, sunny but temp is +- 10 deg C Need to show the dates from the Monday of one month to the Monday of the next month. This is for a payroll period, thus need to show the dates in between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that users can capture the respective hours per employee. Column B would have the respective dates as per the formula, cell B1 would have the start date which the data capturer inputs, cell B4 would be the first Monday of June and then cell B5 would have the Tuesday and then so on until we reach the 2nd July which is the first monday in the new month, thereafter the cells should not show any date. The employees names will be in row 4 Thanks |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculation for Monday of one month to the Monday of the
=$B$1+MOD((7-WEEKDAY($B$1,3)),7)
and for consistency, use this for B5 onwards: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+(7-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),3)),B4+1, ""),"") "Sunnyskies" wrote: Oops another problem, this time with October. The start date even though entered as 2007/10/01 in B1 comes through as 08 October 2007 in cell B4 08 October 2007 Monday 09 October 2007 Tuesday 10 October 2007 Wednesday 11 October 2007 Thursday 12 October 2007 Friday 13 October 2007 Saturday 14 October 2007 Sunday 15 October 2007 Monday 16 October 2007 Tuesday 17 October 2007 Wednesday 18 October 2007 Thursday 19 October 2007 Friday 20 October 2007 Saturday 21 October 2007 Sunday 22 October 2007 Monday 23 October 2007 Tuesday 24 October 2007 Wednesday 25 October 2007 Thursday 26 October 2007 Friday 27 October 2007 Saturday 28 October 2007 Sunday 29 October 2007 Monday 30 October 2007 Tuesday 31 October 2007 Wednesday 01 November 2007 Thursday 02 November 2007 Friday 03 November 2007 Saturday 04 November 2007 Sunday 05 November 2007 Monday "Toppers" wrote: try: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+MOD((8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),7),B4 +1,""),"") "Sunnyskies" wrote: Stop, before you go home. There is a problem with September: 03 September 2007 Monday 04 September 2007 Tuesday 05 September 2007 Wednesday 06 September 2007 Thursday 07 September 2007 Friday 08 September 2007 Saturday 09 September 2007 Sunday 10 September 2007 Monday 11 September 2007 Tuesday 12 September 2007 Wednesday 13 September 2007 Thursday 14 September 2007 Friday 15 September 2007 Saturday 16 September 2007 Sunday 17 September 2007 Monday 18 September 2007 Tuesday 19 September 2007 Wednesday 20 September 2007 Thursday 21 September 2007 Friday 22 September 2007 Saturday 23 September 2007 Sunday 24 September 2007 Monday 25 September 2007 Tuesday 26 September 2007 Wednesday 27 September 2007 Thursday 28 September 2007 Friday 29 September 2007 Saturday 30 September 2007 Sunday 01 October 2007 Monday 02 October 2007 Tuesday 03 October 2007 Wednesday 04 October 2007 Thursday 05 October 2007 Friday 06 October 2007 Saturday 07 October 2007 Sunday 08 October 2007 Monday It should have stopped at the 1st October. Thanks "Toppers" wrote: Try: B1=Start of month e.g 01/06/07 B4: =$B$1+(8-WEEKDAY($B$1,2)) B5: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+(8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),B4+1, ""),"") Copy down until blank cell reached HTH "Sunnyskies" wrote: Morning from a cold SA, sunny but temp is +- 10 deg C Need to show the dates from the Monday of one month to the Monday of the next month. This is for a payroll period, thus need to show the dates in between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that users can capture the respective hours per employee. Column B would have the respective dates as per the formula, cell B1 would have the start date which the data capturer inputs, cell B4 would be the first Monday of June and then cell B5 would have the Tuesday and then so on until we reach the 2nd July which is the first monday in the new month, thereafter the cells should not show any date. The employees names will be in row 4 Thanks |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculation for Monday of one month to the Monday of the
Corrected the formula, but then went back to September to test and it still
does not stop at 01 October 2007, it continues to 08 October 2007. Thanks "Toppers" wrote: =$B$1+MOD((7-WEEKDAY($B$1,3)),7) and for consistency, use this for B5 onwards: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+(7-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),3)),B4+1, ""),"") "Sunnyskies" wrote: Oops another problem, this time with October. The start date even though entered as 2007/10/01 in B1 comes through as 08 October 2007 in cell B4 08 October 2007 Monday 09 October 2007 Tuesday 10 October 2007 Wednesday 11 October 2007 Thursday 12 October 2007 Friday 13 October 2007 Saturday 14 October 2007 Sunday 15 October 2007 Monday 16 October 2007 Tuesday 17 October 2007 Wednesday 18 October 2007 Thursday 19 October 2007 Friday 20 October 2007 Saturday 21 October 2007 Sunday 22 October 2007 Monday 23 October 2007 Tuesday 24 October 2007 Wednesday 25 October 2007 Thursday 26 October 2007 Friday 27 October 2007 Saturday 28 October 2007 Sunday 29 October 2007 Monday 30 October 2007 Tuesday 31 October 2007 Wednesday 01 November 2007 Thursday 02 November 2007 Friday 03 November 2007 Saturday 04 November 2007 Sunday 05 November 2007 Monday "Toppers" wrote: try: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+MOD((8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),7),B4 +1,""),"") "Sunnyskies" wrote: Stop, before you go home. There is a problem with September: 03 September 2007 Monday 04 September 2007 Tuesday 05 September 2007 Wednesday 06 September 2007 Thursday 07 September 2007 Friday 08 September 2007 Saturday 09 September 2007 Sunday 10 September 2007 Monday 11 September 2007 Tuesday 12 September 2007 Wednesday 13 September 2007 Thursday 14 September 2007 Friday 15 September 2007 Saturday 16 September 2007 Sunday 17 September 2007 Monday 18 September 2007 Tuesday 19 September 2007 Wednesday 20 September 2007 Thursday 21 September 2007 Friday 22 September 2007 Saturday 23 September 2007 Sunday 24 September 2007 Monday 25 September 2007 Tuesday 26 September 2007 Wednesday 27 September 2007 Thursday 28 September 2007 Friday 29 September 2007 Saturday 30 September 2007 Sunday 01 October 2007 Monday 02 October 2007 Tuesday 03 October 2007 Wednesday 04 October 2007 Thursday 05 October 2007 Friday 06 October 2007 Saturday 07 October 2007 Sunday 08 October 2007 Monday It should have stopped at the 1st October. Thanks "Toppers" wrote: Try: B1=Start of month e.g 01/06/07 B4: =$B$1+(8-WEEKDAY($B$1,2)) B5: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+(8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),B4+1, ""),"") Copy down until blank cell reached HTH "Sunnyskies" wrote: Morning from a cold SA, sunny but temp is +- 10 deg C Need to show the dates from the Monday of one month to the Monday of the next month. This is for a payroll period, thus need to show the dates in between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that users can capture the respective hours per employee. Column B would have the respective dates as per the formula, cell B1 would have the start date which the data capturer inputs, cell B4 would be the first Monday of June and then cell B5 would have the Tuesday and then so on until we reach the 2nd July which is the first monday in the new month, thereafter the cells should not show any date. The employees names will be in row 4 Thanks |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculation for Monday of one month to the Monday of the
My test test for September : first Monday is 03/09/2007 and last Monday is
01/10/2007, using either formulae.. Are the other dates "left" from previous calculations? "Sunnyskies" wrote: Corrected the formula, but then went back to September to test and it still does not stop at 01 October 2007, it continues to 08 October 2007. Thanks "Toppers" wrote: =$B$1+MOD((7-WEEKDAY($B$1,3)),7) and for consistency, use this for B5 onwards: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+(7-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),3)),B4+1, ""),"") "Sunnyskies" wrote: Oops another problem, this time with October. The start date even though entered as 2007/10/01 in B1 comes through as 08 October 2007 in cell B4 08 October 2007 Monday 09 October 2007 Tuesday 10 October 2007 Wednesday 11 October 2007 Thursday 12 October 2007 Friday 13 October 2007 Saturday 14 October 2007 Sunday 15 October 2007 Monday 16 October 2007 Tuesday 17 October 2007 Wednesday 18 October 2007 Thursday 19 October 2007 Friday 20 October 2007 Saturday 21 October 2007 Sunday 22 October 2007 Monday 23 October 2007 Tuesday 24 October 2007 Wednesday 25 October 2007 Thursday 26 October 2007 Friday 27 October 2007 Saturday 28 October 2007 Sunday 29 October 2007 Monday 30 October 2007 Tuesday 31 October 2007 Wednesday 01 November 2007 Thursday 02 November 2007 Friday 03 November 2007 Saturday 04 November 2007 Sunday 05 November 2007 Monday "Toppers" wrote: try: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+MOD((8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),7),B4 +1,""),"") "Sunnyskies" wrote: Stop, before you go home. There is a problem with September: 03 September 2007 Monday 04 September 2007 Tuesday 05 September 2007 Wednesday 06 September 2007 Thursday 07 September 2007 Friday 08 September 2007 Saturday 09 September 2007 Sunday 10 September 2007 Monday 11 September 2007 Tuesday 12 September 2007 Wednesday 13 September 2007 Thursday 14 September 2007 Friday 15 September 2007 Saturday 16 September 2007 Sunday 17 September 2007 Monday 18 September 2007 Tuesday 19 September 2007 Wednesday 20 September 2007 Thursday 21 September 2007 Friday 22 September 2007 Saturday 23 September 2007 Sunday 24 September 2007 Monday 25 September 2007 Tuesday 26 September 2007 Wednesday 27 September 2007 Thursday 28 September 2007 Friday 29 September 2007 Saturday 30 September 2007 Sunday 01 October 2007 Monday 02 October 2007 Tuesday 03 October 2007 Wednesday 04 October 2007 Thursday 05 October 2007 Friday 06 October 2007 Saturday 07 October 2007 Sunday 08 October 2007 Monday It should have stopped at the 1st October. Thanks "Toppers" wrote: Try: B1=Start of month e.g 01/06/07 B4: =$B$1+(8-WEEKDAY($B$1,2)) B5: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+(8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),B4+1, ""),"") Copy down until blank cell reached HTH "Sunnyskies" wrote: Morning from a cold SA, sunny but temp is +- 10 deg C Need to show the dates from the Monday of one month to the Monday of the next month. This is for a payroll period, thus need to show the dates in between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that users can capture the respective hours per employee. Column B would have the respective dates as per the formula, cell B1 would have the start date which the data capturer inputs, cell B4 would be the first Monday of June and then cell B5 would have the Tuesday and then so on until we reach the 2nd July which is the first monday in the new month, thereafter the cells should not show any date. The employees names will be in row 4 Thanks |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculation for Monday of one month to the Monday of the
Just copied the formulas again, and still get 08 October as the last Monday.
Possible to get your e-mail address so that can send you the file? Thanks "Toppers" wrote: My test test for September : first Monday is 03/09/2007 and last Monday is 01/10/2007, using either formulae.. Are the other dates "left" from previous calculations? "Sunnyskies" wrote: Corrected the formula, but then went back to September to test and it still does not stop at 01 October 2007, it continues to 08 October 2007. Thanks "Toppers" wrote: =$B$1+MOD((7-WEEKDAY($B$1,3)),7) and for consistency, use this for B5 onwards: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+(7-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),3)),B4+1, ""),"") "Sunnyskies" wrote: Oops another problem, this time with October. The start date even though entered as 2007/10/01 in B1 comes through as 08 October 2007 in cell B4 08 October 2007 Monday 09 October 2007 Tuesday 10 October 2007 Wednesday 11 October 2007 Thursday 12 October 2007 Friday 13 October 2007 Saturday 14 October 2007 Sunday 15 October 2007 Monday 16 October 2007 Tuesday 17 October 2007 Wednesday 18 October 2007 Thursday 19 October 2007 Friday 20 October 2007 Saturday 21 October 2007 Sunday 22 October 2007 Monday 23 October 2007 Tuesday 24 October 2007 Wednesday 25 October 2007 Thursday 26 October 2007 Friday 27 October 2007 Saturday 28 October 2007 Sunday 29 October 2007 Monday 30 October 2007 Tuesday 31 October 2007 Wednesday 01 November 2007 Thursday 02 November 2007 Friday 03 November 2007 Saturday 04 November 2007 Sunday 05 November 2007 Monday "Toppers" wrote: try: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+MOD((8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),7),B4 +1,""),"") "Sunnyskies" wrote: Stop, before you go home. There is a problem with September: 03 September 2007 Monday 04 September 2007 Tuesday 05 September 2007 Wednesday 06 September 2007 Thursday 07 September 2007 Friday 08 September 2007 Saturday 09 September 2007 Sunday 10 September 2007 Monday 11 September 2007 Tuesday 12 September 2007 Wednesday 13 September 2007 Thursday 14 September 2007 Friday 15 September 2007 Saturday 16 September 2007 Sunday 17 September 2007 Monday 18 September 2007 Tuesday 19 September 2007 Wednesday 20 September 2007 Thursday 21 September 2007 Friday 22 September 2007 Saturday 23 September 2007 Sunday 24 September 2007 Monday 25 September 2007 Tuesday 26 September 2007 Wednesday 27 September 2007 Thursday 28 September 2007 Friday 29 September 2007 Saturday 30 September 2007 Sunday 01 October 2007 Monday 02 October 2007 Tuesday 03 October 2007 Wednesday 04 October 2007 Thursday 05 October 2007 Friday 06 October 2007 Saturday 07 October 2007 Sunday 08 October 2007 Monday It should have stopped at the 1st October. Thanks "Toppers" wrote: Try: B1=Start of month e.g 01/06/07 B4: =$B$1+(8-WEEKDAY($B$1,2)) B5: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+(8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),B4+1, ""),"") Copy down until blank cell reached HTH "Sunnyskies" wrote: Morning from a cold SA, sunny but temp is +- 10 deg C Need to show the dates from the Monday of one month to the Monday of the next month. This is for a payroll period, thus need to show the dates in between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that users can capture the respective hours per employee. Column B would have the respective dates as per the formula, cell B1 would have the start date which the data capturer inputs, cell B4 would be the first Monday of June and then cell B5 would have the Tuesday and then so on until we reach the 2nd July which is the first monday in the new month, thereafter the cells should not show any date. The employees names will be in row 4 Thanks |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculation for Monday of one month to the Monday of the
toppers <at NOSPAMjohntopley.fsnet.co.uk
Remove NOSPAM from above. "Sunnyskies" wrote: Just copied the formulas again, and still get 08 October as the last Monday. Possible to get your e-mail address so that can send you the file? Thanks "Toppers" wrote: My test test for September : first Monday is 03/09/2007 and last Monday is 01/10/2007, using either formulae.. Are the other dates "left" from previous calculations? "Sunnyskies" wrote: Corrected the formula, but then went back to September to test and it still does not stop at 01 October 2007, it continues to 08 October 2007. Thanks "Toppers" wrote: =$B$1+MOD((7-WEEKDAY($B$1,3)),7) and for consistency, use this for B5 onwards: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+(7-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),3)),B4+1, ""),"") "Sunnyskies" wrote: Oops another problem, this time with October. The start date even though entered as 2007/10/01 in B1 comes through as 08 October 2007 in cell B4 08 October 2007 Monday 09 October 2007 Tuesday 10 October 2007 Wednesday 11 October 2007 Thursday 12 October 2007 Friday 13 October 2007 Saturday 14 October 2007 Sunday 15 October 2007 Monday 16 October 2007 Tuesday 17 October 2007 Wednesday 18 October 2007 Thursday 19 October 2007 Friday 20 October 2007 Saturday 21 October 2007 Sunday 22 October 2007 Monday 23 October 2007 Tuesday 24 October 2007 Wednesday 25 October 2007 Thursday 26 October 2007 Friday 27 October 2007 Saturday 28 October 2007 Sunday 29 October 2007 Monday 30 October 2007 Tuesday 31 October 2007 Wednesday 01 November 2007 Thursday 02 November 2007 Friday 03 November 2007 Saturday 04 November 2007 Sunday 05 November 2007 Monday "Toppers" wrote: try: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+MOD((8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),7),B4 +1,""),"") "Sunnyskies" wrote: Stop, before you go home. There is a problem with September: 03 September 2007 Monday 04 September 2007 Tuesday 05 September 2007 Wednesday 06 September 2007 Thursday 07 September 2007 Friday 08 September 2007 Saturday 09 September 2007 Sunday 10 September 2007 Monday 11 September 2007 Tuesday 12 September 2007 Wednesday 13 September 2007 Thursday 14 September 2007 Friday 15 September 2007 Saturday 16 September 2007 Sunday 17 September 2007 Monday 18 September 2007 Tuesday 19 September 2007 Wednesday 20 September 2007 Thursday 21 September 2007 Friday 22 September 2007 Saturday 23 September 2007 Sunday 24 September 2007 Monday 25 September 2007 Tuesday 26 September 2007 Wednesday 27 September 2007 Thursday 28 September 2007 Friday 29 September 2007 Saturday 30 September 2007 Sunday 01 October 2007 Monday 02 October 2007 Tuesday 03 October 2007 Wednesday 04 October 2007 Thursday 05 October 2007 Friday 06 October 2007 Saturday 07 October 2007 Sunday 08 October 2007 Monday It should have stopped at the 1st October. Thanks "Toppers" wrote: Try: B1=Start of month e.g 01/06/07 B4: =$B$1+(8-WEEKDAY($B$1,2)) B5: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+(8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),B4+1, ""),"") Copy down until blank cell reached HTH "Sunnyskies" wrote: Morning from a cold SA, sunny but temp is +- 10 deg C Need to show the dates from the Monday of one month to the Monday of the next month. This is for a payroll period, thus need to show the dates in between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that users can capture the respective hours per employee. Column B would have the respective dates as per the formula, cell B1 would have the start date which the data capturer inputs, cell B4 would be the first Monday of June and then cell B5 would have the Tuesday and then so on until we reach the 2nd July which is the first monday in the new month, thereafter the cells should not show any date. The employees names will be in row 4 Thanks |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculation for Monday of one month to the Monday of the
Gone.
"Toppers" wrote: toppers <at NOSPAMjohntopley.fsnet.co.uk Remove NOSPAM from above. "Sunnyskies" wrote: Just copied the formulas again, and still get 08 October as the last Monday. Possible to get your e-mail address so that can send you the file? Thanks "Toppers" wrote: My test test for September : first Monday is 03/09/2007 and last Monday is 01/10/2007, using either formulae.. Are the other dates "left" from previous calculations? "Sunnyskies" wrote: Corrected the formula, but then went back to September to test and it still does not stop at 01 October 2007, it continues to 08 October 2007. Thanks "Toppers" wrote: =$B$1+MOD((7-WEEKDAY($B$1,3)),7) and for consistency, use this for B5 onwards: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+(7-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),3)),B4+1, ""),"") "Sunnyskies" wrote: Oops another problem, this time with October. The start date even though entered as 2007/10/01 in B1 comes through as 08 October 2007 in cell B4 08 October 2007 Monday 09 October 2007 Tuesday 10 October 2007 Wednesday 11 October 2007 Thursday 12 October 2007 Friday 13 October 2007 Saturday 14 October 2007 Sunday 15 October 2007 Monday 16 October 2007 Tuesday 17 October 2007 Wednesday 18 October 2007 Thursday 19 October 2007 Friday 20 October 2007 Saturday 21 October 2007 Sunday 22 October 2007 Monday 23 October 2007 Tuesday 24 October 2007 Wednesday 25 October 2007 Thursday 26 October 2007 Friday 27 October 2007 Saturday 28 October 2007 Sunday 29 October 2007 Monday 30 October 2007 Tuesday 31 October 2007 Wednesday 01 November 2007 Thursday 02 November 2007 Friday 03 November 2007 Saturday 04 November 2007 Sunday 05 November 2007 Monday "Toppers" wrote: try: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+MOD((8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),7),B4 +1,""),"") "Sunnyskies" wrote: Stop, before you go home. There is a problem with September: 03 September 2007 Monday 04 September 2007 Tuesday 05 September 2007 Wednesday 06 September 2007 Thursday 07 September 2007 Friday 08 September 2007 Saturday 09 September 2007 Sunday 10 September 2007 Monday 11 September 2007 Tuesday 12 September 2007 Wednesday 13 September 2007 Thursday 14 September 2007 Friday 15 September 2007 Saturday 16 September 2007 Sunday 17 September 2007 Monday 18 September 2007 Tuesday 19 September 2007 Wednesday 20 September 2007 Thursday 21 September 2007 Friday 22 September 2007 Saturday 23 September 2007 Sunday 24 September 2007 Monday 25 September 2007 Tuesday 26 September 2007 Wednesday 27 September 2007 Thursday 28 September 2007 Friday 29 September 2007 Saturday 30 September 2007 Sunday 01 October 2007 Monday 02 October 2007 Tuesday 03 October 2007 Wednesday 04 October 2007 Thursday 05 October 2007 Friday 06 October 2007 Saturday 07 October 2007 Sunday 08 October 2007 Monday It should have stopped at the 1st October. Thanks "Toppers" wrote: Try: B1=Start of month e.g 01/06/07 B4: =$B$1+(8-WEEKDAY($B$1,2)) B5: =IF(B4<"",IF(B4+1<=DATE(YEAR($B$1),MONTH($B$1)+1, 1)+(8-WEEKDAY(DATE(YEAR($B$1),MONTH($B$1)+1,1),2)),B4+1, ""),"") Copy down until blank cell reached HTH "Sunnyskies" wrote: Morning from a cold SA, sunny but temp is +- 10 deg C Need to show the dates from the Monday of one month to the Monday of the next month. This is for a payroll period, thus need to show the dates in between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that users can capture the respective hours per employee. Column B would have the respective dates as per the formula, cell B1 would have the start date which the data capturer inputs, cell B4 would be the first Monday of June and then cell B5 would have the Tuesday and then so on until we reach the 2nd July which is the first monday in the new month, thereafter the cells should not show any date. The employees names will be in row 4 Thanks |
|
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculation for Monday of one month to the Monday of the next
On Thu, 28 Jun 2007 01:44:05 -0700, Sunnyskies
wrote: Morning from a cold SA, sunny but temp is +- 10 deg C Need to show the dates from the Monday of one month to the Monday of the next month. This is for a payroll period, thus need to show the dates in between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that users can capture the respective hours per employee. Column B would have the respective dates as per the formula, cell B1 would have the start date which the data capturer inputs, cell B4 would be the first Monday of June and then cell B5 would have the Tuesday and then so on until we reach the 2nd July which is the first monday in the new month, thereafter the cells should not show any date. The employees names will be in row 4 Thanks Try this: B1: User supplied date If the employees names are in Row 4, how is the first Monday in B4? In any event, to have the first Monday of the Month supplied by the User in B1, B4: =$B$1-DAY($B$1)+8-WEEKDAY($B$1-DAY($B$1)+6) Then, B5: =IF($B$4+ROWS($1:1)$B$4-DAY($B$4)+40- DAY($B$4-DAY($B$4)+32)-WEEKDAY($B$4- DAY($B$4)+32-DAY($B$4-DAY($B$4)+32)+6),"",$B$4+ROWS($1:1)) copy/drag down as far as required. It will blank any dates that might be beyond the 1st Monday of the next month. One potential issue: If the user supplied date is in the subsequent month, but prior to the first Monday of that month, then B4 will contain the first Monday of that subsequent month. If this is an issue, let me know. --ron |
|
#19
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculation for Monday of one month to the Monday of the
Morning Ron,
Users have changed their minds (how unusual ; ) ). They want from the 1st Monday of the month to the first Sundy of the next month. Can you please amend your formula according to the new criteria? Thanks "Ron Rosenfeld" wrote: On Thu, 28 Jun 2007 01:44:05 -0700, Sunnyskies wrote: Morning from a cold SA, sunny but temp is +- 10 deg C Need to show the dates from the Monday of one month to the Monday of the next month. This is for a payroll period, thus need to show the dates in between the two Mondays (i.e Monday 4th June to Monday 2nd July) so that users can capture the respective hours per employee. Column B would have the respective dates as per the formula, cell B1 would have the start date which the data capturer inputs, cell B4 would be the first Monday of June and then cell B5 would have the Tuesday and then so on until we reach the 2nd July which is the first monday in the new month, thereafter the cells should not show any date. The employees names will be in row 4 Thanks Try this: B1: User supplied date If the employees names are in Row 4, how is the first Monday in B4? In any event, to have the first Monday of the Month supplied by the User in B1, B4: =$B$1-DAY($B$1)+8-WEEKDAY($B$1-DAY($B$1)+6) Then, B5: =IF($B$4+ROWS($1:1)$B$4-DAY($B$4)+40- DAY($B$4-DAY($B$4)+32)-WEEKDAY($B$4- DAY($B$4)+32-DAY($B$4-DAY($B$4)+32)+6),"",$B$4+ROWS($1:1)) copy/drag down as far as required. It will blank any dates that might be beyond the 1st Monday of the next month. One potential issue: If the user supplied date is in the subsequent month, but prior to the first Monday of that month, then B4 will contain the first Monday of that subsequent month. If this is an issue, let me know. --ron |
|
#20
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Date calculation for Monday of one month to the Monday of the
On Mon, 2 Jul 2007 00:32:07 -0700, Sunnyskies
wrote: Morning Ron, Users have changed their minds (how unusual ; ) ). They want from the 1st Monday of the month to the first Sundy of the next month. Can you please amend your formula according to the new criteria? Thanks That's a simple modification. Just change the formula in B5 to the one below, and copy/drag down as far as required (at least 34 rows. By the way, what are the dates that the user might enter in B1? The way the formula is written, B4 will return the first Monday of the month entered in B1, and not necessarily the starting date of the period that includes B1. For example, B1: 1 Dec 2007 B4: 3 Dec 2007 B1: 30 Nov 2007 B2: 5 Nov 2007 ====================================== B5: =IF($B$4+ROWS($1:1)$B$4-DAY($B$4)+40-DAY($B$4- DAY($B$4)+32)-WEEKDAY($B$4-DAY($B$4)+32-DAY( $B$4-DAY($B$4)+32)),"",$B$4+ROWS($1:1)) --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need cell formulas to return the day of every Monday in a month based on year entered | Excel Discussion (Misc queries) | |||
| Calculating first/last Monday, Tuesday, etc. in a given month in E | Excel Worksheet Functions | |||
| 1st Monday of a month in date range?? | Excel Worksheet Functions | |||
| Finding the date on the 'nth' Monday in this Month in this Year | Excel Worksheet Functions | |||
| Date to a day (Monday, Tue, etc?) | Excel Discussion (Misc queries) | |||
Linear Mode
