Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Walterius
 
Posts: n/a
Default Calculating recurring date in following month, calculating # days in that period

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


  #2   Report Post  
Duke Carey
 
Posts: n/a
Default

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" wrote:

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



  #3   Report Post  
Walterius
 
Posts: n/a
Default

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?

"Duke Carey" wrote in message
...
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" wrote:

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





  #4   Report Post  
Myrna Larson
 
Posts: n/a
Default

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

On Sat, 4 Jun 2005 11:35:07 -0400, "Walterius" wrote:

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?

"Duke Carey" wrote in message
...
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" wrote:

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





  #5   Report Post  
Walterius
 
Posts: n/a
Default

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

"Myrna Larson" wrote in message
...
Format the result cell as General or Numeric rather than as a date.

On Sat, 4 Jun 2005 11:35:07 -0400, "Walterius"

wrote:

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?

"Duke Carey" wrote in message
...
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" wrote:

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









  #6   Report Post  
Dave Peterson
 
Posts: n/a
Default

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

Sometimes xl can be too helpful.

Walterius wrote:

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

"Myrna Larson" wrote in message
...
Format the result cell as General or Numeric rather than as a date.

On Sat, 4 Jun 2005 11:35:07 -0400, "Walterius"

wrote:

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?

"Duke Carey" wrote in message
...
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" wrote:

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
  #7   Report Post  
Walterius
 
Posts: n/a
Default

Dave, I think you're right. Many thanks to all.
"Dave Peterson" wrote in message
...
I think it changes not when xl recalculates--but when you edit the

formula.

Sometimes xl can be too helpful.

Walterius wrote:

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

"Myrna Larson" wrote in message
...
Format the result cell as General or Numeric rather than as a date.

On Sat, 4 Jun 2005 11:35:07 -0400, "Walterius"

wrote:

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?

"Duke Carey" wrote in message
...
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" wrote:

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup the month in a date string 01/03/05 Una Excel Worksheet Functions 1 March 30th 05 09:45 AM
Calculating days between current date and a date in future NETWORKDAYS() function Faheem Khan Excel Worksheet Functions 2 February 10th 05 08:18 PM
Month Year Date Format Jamie Excel Worksheet Functions 2 February 7th 05 07:43 PM
Calculating due date? Loyalise Excel Worksheet Functions 2 November 2nd 04 08:38 AM
Return the end of month date from a date Steve F. Excel Worksheet Functions 3 October 28th 04 06:17 PM


All times are GMT +1. The time now is 05:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"