Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Date Calculations
Hope I can explain this clearly. If this, or something like it, has been
answered before, please direct me to the post. I have a worksheet in which I need to calculate the future date, one month from the date in another cell, less one day. (ex. 5/18/2005 to 6/17/2005). If the calculated day is a weekend (Saturday or Sunday) I need to return the immediately previous Friday. So, if I am calculating the date based on an original entry of 5/20/2005, the date may come back as 6/19/2005, a Sunday, so I would instead need to get to 6/17/2005. Presently I see that this would probably entail a LOT of nested functions to cover February and the move from December to January, etc. Just wondering if there's a simpler way to approach this (aside from just manually entering the dates after visually determining the correct date). Thanks in advance! |
#2
|
|||
|
|||
One way
assume that A1 holds the sstart date and A2 holds the number of days you want to add to A1 =IF(WEEKDAY(A1+A2,2)5,(A1+A2)-WEEKDAY((A1+A2)-6),A1+A2) Regards, Peo Sjoblom "Bruce" wrote: Hope I can explain this clearly. If this, or something like it, has been answered before, please direct me to the post. I have a worksheet in which I need to calculate the future date, one month from the date in another cell, less one day. (ex. 5/18/2005 to 6/17/2005). If the calculated day is a weekend (Saturday or Sunday) I need to return the immediately previous Friday. So, if I am calculating the date based on an original entry of 5/20/2005, the date may come back as 6/19/2005, a Sunday, so I would instead need to get to 6/17/2005. Presently I see that this would probably entail a LOT of nested functions to cover February and the move from December to January, etc. Just wondering if there's a simpler way to approach this (aside from just manually entering the dates after visually determining the correct date). Thanks in advance! |
#3
|
|||
|
|||
this can certainly be shortened, but it works:
=DATE(YEAR(I1),MONTH(I1)+1,DAY(I1))-1 - (WEEKDAY(DATE(YEAR(I1),MONTH(I1)+1,DAY(I1))-1,3)=5) - (WEEKDAY(DATE(YEAR(I1),MONTH(I1)+1,DAY(I1))-1,3)=6) In article , "Bruce" wrote: Hope I can explain this clearly. If this, or something like it, has been answered before, please direct me to the post. I have a worksheet in which I need to calculate the future date, one month from the date in another cell, less one day. (ex. 5/18/2005 to 6/17/2005). If the calculated day is a weekend (Saturday or Sunday) I need to return the immediately previous Friday. So, if I am calculating the date based on an original entry of 5/20/2005, the date may come back as 6/19/2005, a Sunday, so I would instead need to get to 6/17/2005. Presently I see that this would probably entail a LOT of nested functions to cover February and the move from December to January, etc. Just wondering if there's a simpler way to approach this (aside from just manually entering the dates after visually determining the correct date). Thanks in advance! |
#4
|
|||
|
|||
On Wed, 18 May 2005 13:25:02 -0700, "Bruce"
wrote: Hope I can explain this clearly. If this, or something like it, has been answered before, please direct me to the post. I have a worksheet in which I need to calculate the future date, one month from the date in another cell, less one day. (ex. 5/18/2005 to 6/17/2005). If the calculated day is a weekend (Saturday or Sunday) I need to return the immediately previous Friday. So, if I am calculating the date based on an original entry of 5/20/2005, the date may come back as 6/19/2005, a Sunday, so I would instead need to get to 6/17/2005. Presently I see that this would probably entail a LOT of nested functions to cover February and the move from December to January, etc. Just wondering if there's a simpler way to approach this (aside from just manually entering the dates after visually determining the correct date). Thanks in advance! The problem, as you have realized, is that "month" does not have a definite number of days. So you have to define what you want to happen when the subsequent month has fewer days than the base month. Also, is it possible for the base date to occur on a weekend? For example: Monday 31 Jan 2005 -- ?? --ron |
#5
|
|||
|
|||
Thank you, Peo, but not quite what I needed. Obviously my original
explanantion was lacking. Assume A1 is the original date. I need to calculate A2 from A1 as a the same date the following month less one day, then test to make sure it's not a Saturday or Sunday. If it IS a weekend, I need to back up to the the Friday just prior. So, for example, if A1=5/20/2005 then A2 would be 6/19/2005. That is a Sunday, so I would need to test for that and instead calculate A2 as 6/17/2005. The various differing month end dates, particularly February, may well cause a unique wrinkle in this. "Peo Sjoblom" wrote: One way assume that A1 holds the sstart date and A2 holds the number of days you want to add to A1 =IF(WEEKDAY(A1+A2,2)5,(A1+A2)-WEEKDAY((A1+A2)-6),A1+A2) Regards, Peo Sjoblom "Bruce" wrote: Hope I can explain this clearly. If this, or something like it, has been answered before, please direct me to the post. I have a worksheet in which I need to calculate the future date, one month from the date in another cell, less one day. (ex. 5/18/2005 to 6/17/2005). If the calculated day is a weekend (Saturday or Sunday) I need to return the immediately previous Friday. So, if I am calculating the date based on an original entry of 5/20/2005, the date may come back as 6/19/2005, a Sunday, so I would instead need to get to 6/17/2005. Presently I see that this would probably entail a LOT of nested functions to cover February and the move from December to January, etc. Just wondering if there's a simpler way to approach this (aside from just manually entering the dates after visually determining the correct date). Thanks in advance! |
#6
|
|||
|
|||
Thank you, JE. I will test this out and see if it is what I need.
"JE McGimpsey" wrote: this can certainly be shortened, but it works: =DATE(YEAR(I1),MONTH(I1)+1,DAY(I1))-1 - (WEEKDAY(DATE(YEAR(I1),MONTH(I1)+1,DAY(I1))-1,3)=5) - (WEEKDAY(DATE(YEAR(I1),MONTH(I1)+1,DAY(I1))-1,3)=6) In article , "Bruce" wrote: Hope I can explain this clearly. If this, or something like it, has been answered before, please direct me to the post. I have a worksheet in which I need to calculate the future date, one month from the date in another cell, less one day. (ex. 5/18/2005 to 6/17/2005). If the calculated day is a weekend (Saturday or Sunday) I need to return the immediately previous Friday. So, if I am calculating the date based on an original entry of 5/20/2005, the date may come back as 6/19/2005, a Sunday, so I would instead need to get to 6/17/2005. Presently I see that this would probably entail a LOT of nested functions to cover February and the move from December to January, etc. Just wondering if there's a simpler way to approach this (aside from just manually entering the dates after visually determining the correct date). Thanks in advance! |
#7
|
|||
|
|||
Thanks, Ron, for your reply.
No, the base date from which I begin the calculations will never be anything but a weekday. Bruce "Ron Rosenfeld" wrote: On Wed, 18 May 2005 13:25:02 -0700, "Bruce" wrote: Hope I can explain this clearly. If this, or something like it, has been answered before, please direct me to the post. I have a worksheet in which I need to calculate the future date, one month from the date in another cell, less one day. (ex. 5/18/2005 to 6/17/2005). If the calculated day is a weekend (Saturday or Sunday) I need to return the immediately previous Friday. So, if I am calculating the date based on an original entry of 5/20/2005, the date may come back as 6/19/2005, a Sunday, so I would instead need to get to 6/17/2005. Presently I see that this would probably entail a LOT of nested functions to cover February and the move from December to January, etc. Just wondering if there's a simpler way to approach this (aside from just manually entering the dates after visually determining the correct date). Thanks in advance! The problem, as you have realized, is that "month" does not have a definite number of days. So you have to define what you want to happen when the subsequent month has fewer days than the base month. Also, is it possible for the base date to occur on a weekend? For example: Monday 31 Jan 2005 -- ?? --ron |
#8
|
|||
|
|||
Bruce
While the formla from McGimpsey works great and technically gives you what you asked for, you need to pay close attention to what Ron is saying. Sample output from month ends: Saturday, May 28, 2005 Monday, June 27, 2005 Sunday, May 29, 2005 Tuesday, June 28, 2005 Monday, May 30, 2005 Wednesday, June 29, 2005 Tuesday, May 31, 2005 Thursday, June 30, 2005 Wednesday, June 01, 2005 Thursday, June 30, 2005 Thursday, June 02, 2005 Friday, July 01, 2005 Friday, June 03, 2005 Friday, July 01, 2005 Saturday, June 04, 2005 Friday, July 01, 2005 Sunday, June 05, 2005 Monday, July 04, 2005 Monday, June 06, 2005 Tuesday, July 05, 2005 Tuesday, June 07, 2005 Wednesday, July 06, 2005 Wednesday, June 08, 2005 Thursday, July 07, 2005 Thursday, June 09, 2005 Friday, July 08, 2005 Friday, June 10, 2005 Friday, July 08, 2005 Saturday, June 11, 2005 Friday, July 08, 2005 The normal pattern is M-Tu-W-Th-FFF-M-Tu-W-Th-FFF... around the end of a month, if the preceding month is 31 days and the next month is 30 days, the pattern is changed, it changes if the lengths are reversed, too. Note in the example above, thursday is repeated twice. Below Friday is only repeated twice, this may be ok, but it is out of pattern. Sunday, June 26, 2005 Monday, July 25, 2005 Monday, June 27, 2005 Tuesday, July 26, 2005 Tuesday, June 28, 2005 Wednesday, July 27, 2005 Wednesday, June 29, 2005 Thursday, July 28, 2005 Thursday, June 30, 2005 Friday, July 29, 2005 Friday, July 01, 2005 Friday, July 29, 2005 Saturday, July 02, 2005 Monday, August 01, 2005 Sunday, July 03, 2005 Tuesday, August 02, 2005 Monday, July 04, 2005 Wednesday, August 03, 2005 Tuesday, July 05, 2005 Thursday, August 04, 2005 Wednesday, July 06, 2005 Friday, August 05, 2005 Thursday, July 07, 2005 Friday, August 05, 2005 Friday, July 08, 2005 Friday, August 05, 2005 Saturday, July 09, 2005 Monday, August 08, 2005 Sunday, July 10, 2005 Tuesday, August 09, 2005 Below, two months with 31 days appears to be normal, MTWTFFF. Friday, July 29, 2005 Friday, August 26, 2005 Saturday, July 30, 2005 Monday, August 29, 2005 Sunday, July 31, 2005 Tuesday, August 30, 2005 Monday, August 01, 2005 Wednesday, August 31, 2005 Tuesday, August 02, 2005 Thursday, September 01, 2005 Wednesday, August 03, 2005 Friday, September 02, 2005 Thursday, August 04, 2005 Friday, September 02, 2005 Friday, August 05, 2005 Friday, September 02, 2005 Saturday, August 06, 2005 Monday, September 05, 2005 Sunday, August 07, 2005 Tuesday, September 06, 2005 Monday, August 08, 2005 Wednesday, September 07, 2005 But the next month, seen below, it goes haywire, you get friday 4 times in a row. Wait, there's more... Sunday, August 28, 2005 Tuesday, September 27, 2005 Monday, August 29, 2005 Wednesday, September 28, 2005 Tuesday, August 30, 2005 Thursday, September 29, 2005 Wednesday, August 31, 2005 Friday, September 30, 2005 Thursday, September 01, 2005 Friday, September 30, 2005 Friday, September 02, 2005 Friday, September 30, 2005 Saturday, September 03, 2005 Friday, September 30, 2005 Sunday, September 04, 2005 Monday, October 03, 2005 Monday, September 05, 2005 Tuesday, October 04, 2005 Tuesday, September 06, 2005 Wednesday, October 05, 2005 Wednesday, September 07, 2005 Thursday, October 06, 2005 Thursday, September 08, 2005 Friday, October 07, 2005 Here is next January lapping into February... Note the resulting dates jump back a month then back forward again... Sunday, January 29, 2006 Tuesday, February 28, 2006 Monday, January 30, 2006 Wednesday, March 01, 2006 Tuesday, January 31, 2006 Thursday, March 02, 2006 Wednesday, February 01, 2006 Tuesday, February 28, 2006 Thursday, February 02, 2006 Wednesday, March 01, 2006 Friday, February 03, 2006 Thursday, March 02, 2006 Saturday, February 04, 2006 Friday, March 03, 2006 Sunday, February 05, 2006 Friday, March 03, 2006 Monday, February 06, 2006 Friday, March 03, 2006 At the end of Feb 06, you skip almost a week of target dates (well tuesday, wednesday, and thursday) because Feb doesn't have enough days to run to the end of the next month. Saturday, February 25, 2006 Friday, March 24, 2006 Sunday, February 26, 2006 Friday, March 24, 2006 Monday, February 27, 2006 Friday, March 24, 2006 Tuesday, February 28, 2006 Monday, March 27, 2006 Wednesday, March 01, 2006 Friday, March 31, 2006 Thursday, March 02, 2006 Friday, March 31, 2006 Friday, March 03, 2006 Friday, March 31, 2006 Saturday, March 04, 2006 Monday, April 03, 2006 Sunday, March 05, 2006 Tuesday, April 04, 2006 Monday, March 06, 2006 Wednesday, April 05, 2006 Tuesday, March 07, 2006 Thursday, April 06, 2006 So, if this is not what you expected to happen, you (or someone...) needs to tweak the formula a bit. Hope this helps SongBear "Bruce" wrote: Thanks, Ron, for your reply. No, the base date from which I begin the calculations will never be anything but a weekday. Bruce "Ron Rosenfeld" wrote: On Wed, 18 May 2005 13:25:02 -0700, "Bruce" wrote: Hope I can explain this clearly. If this, or something like it, has been answered before, please direct me to the post. I have a worksheet in which I need to calculate the future date, one month from the date in another cell, less one day. (ex. 5/18/2005 to 6/17/2005). If the calculated day is a weekend (Saturday or Sunday) I need to return the immediately previous Friday. So, if I am calculating the date based on an original entry of 5/20/2005, the date may come back as 6/19/2005, a Sunday, so I would instead need to get to 6/17/2005. Presently I see that this would probably entail a LOT of nested functions to cover February and the move from December to January, etc. Just wondering if there's a simpler way to approach this (aside from just manually entering the dates after visually determining the correct date). Thanks in advance! The problem, as you have realized, is that "month" does not have a definite number of days. So you have to define what you want to happen when the subsequent month has fewer days than the base month. Also, is it possible for the base date to occur on a weekend? For example: Monday 31 Jan 2005 -- ?? --ron |
#9
|
|||
|
|||
Bruce, actually the problem may be simpler than you are making it. Think
about the requirement of one month. What does that stand for within your business rules? and where does that minus one day come from, too? Would 28 days (4 weeks) do just as well? One simplification is: that would automatically land you on a week day if you always started on a week day. And it might automatically take care of the need that always subtracting a day is covering. You are automatically approximately a month (4 weeks) later, yet at lease one day or more ahead of an exact month for about 45 months out of 48 (it is the same day on each february except leap years). But if you gotta have the McGimpsey - and it is purty - then i probably will have to be tweaked. Let us know if any of this helped. SongBear "Bruce" wrote: Thanks, Ron, for your reply. No, the base date from which I begin the calculations will never be anything but a weekday. Bruce "Ron Rosenfeld" wrote: On Wed, 18 May 2005 13:25:02 -0700, "Bruce" wrote: Hope I can explain this clearly. If this, or something like it, has been answered before, please direct me to the post. I have a worksheet in which I need to calculate the future date, one month from the date in another cell, less one day. (ex. 5/18/2005 to 6/17/2005). If the calculated day is a weekend (Saturday or Sunday) I need to return the immediately previous Friday. So, if I am calculating the date based on an original entry of 5/20/2005, the date may come back as 6/19/2005, a Sunday, so I would instead need to get to 6/17/2005. Presently I see that this would probably entail a LOT of nested functions to cover February and the move from December to January, etc. Just wondering if there's a simpler way to approach this (aside from just manually entering the dates after visually determining the correct date). Thanks in advance! The problem, as you have realized, is that "month" does not have a definite number of days. So you have to define what you want to happen when the subsequent month has fewer days than the base month. Also, is it possible for the base date to occur on a weekend? For example: Monday 31 Jan 2005 -- ?? --ron |
#10
|
|||
|
|||
SongBear,
A big thank you! You went to a lot of trouble to provide that info for me in such detail... I greatly appreciate it and will, I think, be able to do the tweaking needed now that I have all that info. Had no clue you all here would respond so quickly and thoroughly! Bruce "SongBear" wrote: Bruce While the formla from McGimpsey works great and technically gives you what you asked for, you need to pay close attention to what Ron is saying. Sample output from month ends: Saturday, May 28, 2005 Monday, June 27, 2005 Sunday, May 29, 2005 Tuesday, June 28, 2005 Monday, May 30, 2005 Wednesday, June 29, 2005 Tuesday, May 31, 2005 Thursday, June 30, 2005 Wednesday, June 01, 2005 Thursday, June 30, 2005 Thursday, June 02, 2005 Friday, July 01, 2005 Friday, June 03, 2005 Friday, July 01, 2005 Saturday, June 04, 2005 Friday, July 01, 2005 Sunday, June 05, 2005 Monday, July 04, 2005 Monday, June 06, 2005 Tuesday, July 05, 2005 Tuesday, June 07, 2005 Wednesday, July 06, 2005 Wednesday, June 08, 2005 Thursday, July 07, 2005 Thursday, June 09, 2005 Friday, July 08, 2005 Friday, June 10, 2005 Friday, July 08, 2005 Saturday, June 11, 2005 Friday, July 08, 2005 The normal pattern is M-Tu-W-Th-FFF-M-Tu-W-Th-FFF... around the end of a month, if the preceding month is 31 days and the next month is 30 days, the pattern is changed, it changes if the lengths are reversed, too. Note in the example above, thursday is repeated twice. Below Friday is only repeated twice, this may be ok, but it is out of pattern. Sunday, June 26, 2005 Monday, July 25, 2005 Monday, June 27, 2005 Tuesday, July 26, 2005 Tuesday, June 28, 2005 Wednesday, July 27, 2005 Wednesday, June 29, 2005 Thursday, July 28, 2005 Thursday, June 30, 2005 Friday, July 29, 2005 Friday, July 01, 2005 Friday, July 29, 2005 Saturday, July 02, 2005 Monday, August 01, 2005 Sunday, July 03, 2005 Tuesday, August 02, 2005 Monday, July 04, 2005 Wednesday, August 03, 2005 Tuesday, July 05, 2005 Thursday, August 04, 2005 Wednesday, July 06, 2005 Friday, August 05, 2005 Thursday, July 07, 2005 Friday, August 05, 2005 Friday, July 08, 2005 Friday, August 05, 2005 Saturday, July 09, 2005 Monday, August 08, 2005 Sunday, July 10, 2005 Tuesday, August 09, 2005 Below, two months with 31 days appears to be normal, MTWTFFF. Friday, July 29, 2005 Friday, August 26, 2005 Saturday, July 30, 2005 Monday, August 29, 2005 Sunday, July 31, 2005 Tuesday, August 30, 2005 Monday, August 01, 2005 Wednesday, August 31, 2005 Tuesday, August 02, 2005 Thursday, September 01, 2005 Wednesday, August 03, 2005 Friday, September 02, 2005 Thursday, August 04, 2005 Friday, September 02, 2005 Friday, August 05, 2005 Friday, September 02, 2005 Saturday, August 06, 2005 Monday, September 05, 2005 Sunday, August 07, 2005 Tuesday, September 06, 2005 Monday, August 08, 2005 Wednesday, September 07, 2005 But the next month, seen below, it goes haywire, you get friday 4 times in a row. Wait, there's more... Sunday, August 28, 2005 Tuesday, September 27, 2005 Monday, August 29, 2005 Wednesday, September 28, 2005 Tuesday, August 30, 2005 Thursday, September 29, 2005 Wednesday, August 31, 2005 Friday, September 30, 2005 Thursday, September 01, 2005 Friday, September 30, 2005 Friday, September 02, 2005 Friday, September 30, 2005 Saturday, September 03, 2005 Friday, September 30, 2005 Sunday, September 04, 2005 Monday, October 03, 2005 Monday, September 05, 2005 Tuesday, October 04, 2005 Tuesday, September 06, 2005 Wednesday, October 05, 2005 Wednesday, September 07, 2005 Thursday, October 06, 2005 Thursday, September 08, 2005 Friday, October 07, 2005 Here is next January lapping into February... Note the resulting dates jump back a month then back forward again... Sunday, January 29, 2006 Tuesday, February 28, 2006 Monday, January 30, 2006 Wednesday, March 01, 2006 Tuesday, January 31, 2006 Thursday, March 02, 2006 Wednesday, February 01, 2006 Tuesday, February 28, 2006 Thursday, February 02, 2006 Wednesday, March 01, 2006 Friday, February 03, 2006 Thursday, March 02, 2006 Saturday, February 04, 2006 Friday, March 03, 2006 Sunday, February 05, 2006 Friday, March 03, 2006 Monday, February 06, 2006 Friday, March 03, 2006 At the end of Feb 06, you skip almost a week of target dates (well tuesday, wednesday, and thursday) because Feb doesn't have enough days to run to the end of the next month. Saturday, February 25, 2006 Friday, March 24, 2006 Sunday, February 26, 2006 Friday, March 24, 2006 Monday, February 27, 2006 Friday, March 24, 2006 Tuesday, February 28, 2006 Monday, March 27, 2006 Wednesday, March 01, 2006 Friday, March 31, 2006 Thursday, March 02, 2006 Friday, March 31, 2006 Friday, March 03, 2006 Friday, March 31, 2006 Saturday, March 04, 2006 Monday, April 03, 2006 Sunday, March 05, 2006 Tuesday, April 04, 2006 Monday, March 06, 2006 Wednesday, April 05, 2006 Tuesday, March 07, 2006 Thursday, April 06, 2006 So, if this is not what you expected to happen, you (or someone...) needs to tweak the formula a bit. Hope this helps SongBear "Bruce" wrote: Thanks, Ron, for your reply. No, the base date from which I begin the calculations will never be anything but a weekday. Bruce "Ron Rosenfeld" wrote: On Wed, 18 May 2005 13:25:02 -0700, "Bruce" wrote: Hope I can explain this clearly. If this, or something like it, has been answered before, please direct me to the post. I have a worksheet in which I need to calculate the future date, one month from the date in another cell, less one day. (ex. 5/18/2005 to 6/17/2005). If the calculated day is a weekend (Saturday or Sunday) I need to return the immediately previous Friday. So, if I am calculating the date based on an original entry of 5/20/2005, the date may come back as 6/19/2005, a Sunday, so I would instead need to get to 6/17/2005. Presently I see that this would probably entail a LOT of nested functions to cover February and the move from December to January, etc. Just wondering if there's a simpler way to approach this (aside from just manually entering the dates after visually determining the correct date). Thanks in advance! The problem, as you have realized, is that "month" does not have a definite number of days. So you have to define what you want to happen when the subsequent month has fewer days than the base month. Also, is it possible for the base date to occur on a weekend? For example: Monday 31 Jan 2005 -- ?? --ron |
#11
|
|||
|
|||
On Wed, 18 May 2005 14:24:02 -0700, "Bruce"
wrote: Thanks, Ron, for your reply. No, the base date from which I begin the calculations will never be anything but a weekday. That makes things simpler. But maybe I wasn't entirely clear. You also need to define what you want to happen when the subsequent month has fewer days than the base month. For example: Monday 31 Jan 2005 -- ?? What do you expect for an answer?? --ron |
#12
|
|||
|
|||
SongBear,
You hit the nail on the head. I was thinking along that direction of changing to a set time, regardless of the month ends, etc. So, yes, your input helped a great deal! Again, thank you very much. Bruce "SongBear" wrote: Bruce, actually the problem may be simpler than you are making it. Think about the requirement of one month. What does that stand for within your business rules? and where does that minus one day come from, too? Would 28 days (4 weeks) do just as well? One simplification is: that would automatically land you on a week day if you always started on a week day. And it might automatically take care of the need that always subtracting a day is covering. You are automatically approximately a month (4 weeks) later, yet at lease one day or more ahead of an exact month for about 45 months out of 48 (it is the same day on each february except leap years). But if you gotta have the McGimpsey - and it is purty - then i probably will have to be tweaked. Let us know if any of this helped. SongBear "Bruce" wrote: Thanks, Ron, for your reply. No, the base date from which I begin the calculations will never be anything but a weekday. Bruce "Ron Rosenfeld" wrote: On Wed, 18 May 2005 13:25:02 -0700, "Bruce" wrote: Hope I can explain this clearly. If this, or something like it, has been answered before, please direct me to the post. I have a worksheet in which I need to calculate the future date, one month from the date in another cell, less one day. (ex. 5/18/2005 to 6/17/2005). If the calculated day is a weekend (Saturday or Sunday) I need to return the immediately previous Friday. So, if I am calculating the date based on an original entry of 5/20/2005, the date may come back as 6/19/2005, a Sunday, so I would instead need to get to 6/17/2005. Presently I see that this would probably entail a LOT of nested functions to cover February and the move from December to January, etc. Just wondering if there's a simpler way to approach this (aside from just manually entering the dates after visually determining the correct date). Thanks in advance! The problem, as you have realized, is that "month" does not have a definite number of days. So you have to define what you want to happen when the subsequent month has fewer days than the base month. Also, is it possible for the base date to occur on a weekend? For example: Monday 31 Jan 2005 -- ?? --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
date and bond calculations | Excel Discussion (Misc queries) | |||
date and bond calculations | Excel Worksheet Functions | |||
Problems with date calculations (bank hols etc) | Excel Discussion (Misc queries) | |||
Setting up "Year to Date" Calculations in a Pivot Table | Excel Worksheet Functions | |||
Date and time calculations | Excel Discussion (Misc queries) |