Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
faberk
 
Posts: n/a
Default Trying to build a formula

I am trying to construct a formula to return the first day of the next month:

08-01-1969 = 09-01-1969
11-09-1960 = 12-01-1960
12-01-1970 = 01-01-1971

I dont have a problem with the first two. I use the month function and
increment it by one. This works fine until i run into a date in december.
How can i do this?

  #2   Report Post  
Bernard Liengme
 
Posts: n/a
Default

Try =DATE(YEAR(A1),MONTH(A1)+1,0)+1
The first part =DATE(YEAR(A1),MONTH(A1)+1,0) find the last day of the month
of the date in A1. Adding one does what you want.

best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"faberk" wrote in message
...
I am trying to construct a formula to return the first day of the next
month:

08-01-1969 = 09-01-1969
11-09-1960 = 12-01-1960
12-01-1970 = 01-01-1971

I dont have a problem with the first two. I use the month function and
increment it by one. This works fine until i run into a date in december.
How can i do this?



  #3   Report Post  
Jason Morin
 
Posts: n/a
Default

=DATE(YEAR(A1),MONTH(A1)+1,1)

HTH
Jason
Atlanta, GA

-----Original Message-----
I am trying to construct a formula to return the first

day of the next month:

08-01-1969 = 09-01-1969
11-09-1960 = 12-01-1960
12-01-1970 = 01-01-1971

I dont have a problem with the first two. I use the

month function and
increment it by one. This works fine until i run into a

date in december.
How can i do this?

.

  #4   Report Post  
Niek Otten
 
Posts: n/a
Default

=DATE(YEAR(A1),MONTH(A1)+1,1)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"faberk" wrote in message
...
I am trying to construct a formula to return the first day of the next
month:

08-01-1969 = 09-01-1969
11-09-1960 = 12-01-1960
12-01-1970 = 01-01-1971

I dont have a problem with the first two. I use the month function and
increment it by one. This works fine until i run into a date in december.
How can i do this?



  #5   Report Post  
David Jessop
 
Posts: n/a
Default

Hi,

Running in Excel 2002,
=DATE(YEAR(A1),MONTH(A1)+1,1)
where A1 is the existing date, works fine.

If that doesn't work, then I'd probably do something like
=IF(MONTH(A1)=12, DATE(YEAR(A1)+1,1,1), DATE(YEAR(A1), MONTH(A1)+1, 1)

You could do something within the date function itself, but this is more
obvious (at least to me).

Regards,

David Jessop

"faberk" wrote:

I am trying to construct a formula to return the first day of the next month:

08-01-1969 = 09-01-1969
11-09-1960 = 12-01-1960
12-01-1970 = 01-01-1971

I dont have a problem with the first two. I use the month function and
increment it by one. This works fine until i run into a date in december.
How can i do this?



  #6   Report Post  
Bob Phillips
 
Posts: n/a
Default

Assuming the date is in A1, use

=DATE(YEAR(A1),MONTH(A1)+1,1)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"faberk" wrote in message
...
I am trying to construct a formula to return the first day of the next

month:

08-01-1969 = 09-01-1969
11-09-1960 = 12-01-1960
12-01-1970 = 01-01-1971

I dont have a problem with the first two. I use the month function and
increment it by one. This works fine until i run into a date in december.
How can i do this?



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
revert formula insertion to old method Don't be a pain in the ass Setting up and Configuration of Excel 0 January 24th 05 01:49 PM
Relative Indirect Formula Referencing? Damian Excel Worksheet Functions 1 January 7th 05 04:16 AM
Help with macro formula and variable Huge project Excel Worksheet Functions 0 December 28th 04 01:27 AM
Cell doesn't show formula result - it shows formula (CTRL + ' doe. o0o0o0o Excel Worksheet Functions 6 November 19th 04 03:13 PM
how to build a formula to match numbers in 2 columns with the equ. mcdilash Excel Worksheet Functions 1 November 10th 04 05:31 PM


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

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

About Us

"It's about Microsoft Excel"