Calculating recurring date in following month, calculating # days in that period

[Walterius]

Excel 2002. I have a simple spreadsheet that logs my expenses by month.

I haven't been able to teach Excel how to calculate the same day next month.
E.g., the 3rd of June is 06/03/05, so the 3rd of the next month should be
07/03/05.

I also need the number of days in that period, e.g. from 6/03/05 to 7/03/05
= 30 days (because there are 30 days in June). I don't know how to calculate
that either.

Attempts to use the EOMONTH function fail, and I can't find anything else
that looks suitable. I have been using Excel for years and I am still an
Excel klutz. :-(

Many thanks for your help.

Walterius

 

[Guest]

Instead of EOMONTH(), try EDATE()

=EDATE(earlier date,1)

will give you the same DAY next month. However, if you use it on a Jan 31
start date, it'll give you the lat day of February.

Then just subtract the two dates to get the # of days between them

 

[Walterius]

Many thanks, Duke. The first part works just fine: I get July 3, 2005 as
next month's date.

But when I subtract the two dates, I get January 30, 1900, instead of 30.
Viz:

C2=6/3/2005
B15=EDATE(C2,1)=7/3/2005 [correct]

But =B15-C2 = 1/30/1900

??? What am I doing wrong?

 

[Myrna Larson]

Format the result cell as General or Numeric rather than as a date.

Many thanks, Duke. The first part works just fine: I get July 3, 2005 as
next month's date.

But when I subtract the two dates, I get January 30, 1900, instead of 30.
Viz:

C2=6/3/2005
B15=EDATE(C2,1)=7/3/2005 [correct]

But =B15-C2 = 1/30/1900

??? What am I doing wrong?

Instead of EOMONTH(), try EDATE()

=EDATE(earlier date,1)

will give you the same DAY next month. However, if you use it on a Jan 31
start date, it'll give you the lat day of February.

Then just subtract the two dates to get the # of days between them

 

[Walterius]

Thanks again. FYI, it works with Numeric but only works the first time with
General, because next time it calculates the formula, it changes General
back to Date.

In any event, problems solved. Thanks much!

Walterius

Format the result cell as General or Numeric rather than as a date.

Many thanks, Duke. The first part works just fine: I get July 3, 2005 as
next month's date.

But when I subtract the two dates, I get January 30, 1900, instead of 30.
Viz:

C2=6/3/2005
B15=EDATE(C2,1)=7/3/2005 [correct]

But =B15-C2 = 1/30/1900

??? What am I doing wrong?

Instead of EOMONTH(), try EDATE()

=EDATE(earlier date,1)

will give you the same DAY next month. However, if you use it on a Jan 31
start date, it'll give you the lat day of February.

Then just subtract the two dates to get the # of days between them


:

Excel 2002. I have a simple spreadsheet that logs my expenses by month.

I haven't been able to teach Excel how to calculate the same day next month.
E.g., the 3rd of June is 06/03/05, so the 3rd of the next month

should
be

07/03/05.

I also need the number of days in that period, e.g. from 6/03/05 to 7/03/05
= 30 days (because there are 30 days in June). I don't know how to calculate
that either.

Attempts to use the EOMONTH function fail, and I can't find anything else
that looks suitable. I have been using Excel for years and I am still an
Excel klutz. :-(

Many thanks for your help.

Walterius

 

[Dave Peterson]

I think it changes not when xl recalculates--but when you edit the formula.

Sometimes xl can be too helpful.

Thanks again. FYI, it works with Numeric but only works the first time with
General, because next time it calculates the formula, it changes General
back to Date.

In any event, problems solved. Thanks much!

Walterius

Format the result cell as General or Numeric rather than as a date.

Many thanks, Duke. The first part works just fine: I get July 3, 2005 as
next month's date.

But when I subtract the two dates, I get January 30, 1900, instead of 30.
Viz:

C2=6/3/2005
B15=EDATE(C2,1)=7/3/2005 [correct]

But =B15-C2 = 1/30/1900

??? What am I doing wrong?

Instead of EOMONTH(), try EDATE()

=EDATE(earlier date,1)

will give you the same DAY next month. However, if you use it on a Jan 31
start date, it'll give you the lat day of February.

Then just subtract the two dates to get the # of days between them


:

Excel 2002. I have a simple spreadsheet that logs my expenses by month.

I haven't been able to teach Excel how to calculate the same day next
month.
E.g., the 3rd of June is 06/03/05, so the 3rd of the next month should
be
07/03/05.

I also need the number of days in that period, e.g. from 6/03/05 to
7/03/05
= 30 days (because there are 30 days in June). I don't know how to
calculate
that either.

Attempts to use the EOMONTH function fail, and I can't find anything
else
that looks suitable. I have been using Excel for years and I am still an
Excel klutz. :-(

Many thanks for your help.

Walterius

 

[Walterius]

Dave, I think you're right. Many thanks to all.

I think it changes not when xl recalculates--but when you edit the formula.

Sometimes xl can be too helpful.

Thanks again. FYI, it works with Numeric but only works the first time with
General, because next time it calculates the formula, it changes General
back to Date.

In any event, problems solved. Thanks much!

Walterius

Format the result cell as General or Numeric rather than as a date.

Many thanks, Duke. The first part works just fine: I get July 3, 2005 as
next month's date.

But when I subtract the two dates, I get January 30, 1900, instead of 30.
Viz:

C2=6/3/2005
B15=EDATE(C2,1)=7/3/2005 [correct]

But =B15-C2 = 1/30/1900

??? What am I doing wrong?

Instead of EOMONTH(), try EDATE()

=EDATE(earlier date,1)

will give you the same DAY next month. However, if you use it on a

Jan
31

start date, it'll give you the lat day of February.

Then just subtract the two dates to get the # of days between them


:

Excel 2002. I have a simple spreadsheet that logs my expenses by month.

I haven't been able to teach Excel how to calculate the same day next
month.
E.g., the 3rd of June is 06/03/05, so the 3rd of the next month should
be
07/03/05.

I also need the number of days in that period, e.g. from 6/03/05 to
7/03/05
= 30 days (because there are 30 days in June). I don't know how to
calculate
that either.

Attempts to use the EOMONTH function fail, and I can't find anything
else
that looks suitable. I have been using Excel for years and I am

still
an

Excel klutz. :-(

Many thanks for your help.

Walterius