software

Just to round up (for now) the question on how to manipulate dates, here are
some formulae (which assume the root date is in A1) for advancing dates. It
started as an exercise to try to understand the subject - hence the number
of variations. I hope it will be of use.

1 day
=DATE(YEAR(A1),MONTH(A1),DAY(A1) 1)

1 month
=DATE(YEAR(A1),MONTH(A1) 1,DAY(A1))

1 year
=DATE(YEAR(A1) 1,MONTH(A1),DAY(A1))

1 year, 1 month, 1 day
=DATE(YEAR(A1) 1,MONTH(A1) 1,DAY(A1) 1)

1 year, 1 month
=DATE(YEAR(A1) 1,MONTH(A1) 1,DAY(A1))

First day of following month
=DATE(YEAR(A1),MONTH(A1) 1,1)

Last day of current month
=DATE(YEAR(A1),MONTH(A1) 1,0)

Last day of following month
=DATE(YEAR(A1),MONTH(A1) 1 1,0)

Tenth day of following month
=DATE(YEAR(A1),MONTH(A1) 1,10)

Bill Ridgeway
Computer Solutions
gt;
gt;
gt;
Very handy compilation Bill..........another one for my secret stash.

Thanks for posting it.

Vaya con Dios,
Chuck, CABGx3

quot;Bill Ridgewayquot; wrote:

gt; Just to round up (for now) the question on how to manipulate dates, here are
gt; some formulae (which assume the root date is in A1) for advancing dates. It
gt; started as an exercise to try to understand the subject - hence the number
gt; of variations. I hope it will be of use.
gt;
gt; 1 day
gt; =DATE(YEAR(A1),MONTH(A1),DAY(A1) 1)
gt;
gt; 1 month
gt; =DATE(YEAR(A1),MONTH(A1) 1,DAY(A1))
gt;
gt; 1 year
gt; =DATE(YEAR(A1) 1,MONTH(A1),DAY(A1))
gt;
gt; 1 year, 1 month, 1 day
gt; =DATE(YEAR(A1) 1,MONTH(A1) 1,DAY(A1) 1)
gt;
gt; 1 year, 1 month
gt; =DATE(YEAR(A1) 1,MONTH(A1) 1,DAY(A1))
gt;
gt; First day of following month
gt; =DATE(YEAR(A1),MONTH(A1) 1,1)
gt;
gt; Last day of current month
gt; =DATE(YEAR(A1),MONTH(A1) 1,0)
gt;
gt; Last day of following month
gt; =DATE(YEAR(A1),MONTH(A1) 1 1,0)
gt;
gt; Tenth day of following month
gt; =DATE(YEAR(A1),MONTH(A1) 1,10)
gt;
gt; Bill Ridgeway
gt; Computer Solutions
gt; gt;
gt; gt;
gt; gt;
gt;
gt;
gt;

Chip Pearson also has some great info on dates/times on his website:-

cpearson.com/excel/datearith.htm

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------quot;Bill Ridgewayquot; gt; wrote in message
...
gt; Just to round up (for now) the question on how to manipulate dates, here
gt; are some formulae (which assume the root date is in A1) for advancing
gt; dates. It
gt; started as an exercise to try to understand the subject - hence the number
gt; of variations. I hope it will be of use.
gt;
gt; 1 day
gt; =DATE(YEAR(A1),MONTH(A1),DAY(A1) 1)
gt;
gt; 1 month
gt; =DATE(YEAR(A1),MONTH(A1) 1,DAY(A1))
gt;
gt; 1 year
gt; =DATE(YEAR(A1) 1,MONTH(A1),DAY(A1))
gt;
gt; 1 year, 1 month, 1 day
gt; =DATE(YEAR(A1) 1,MONTH(A1) 1,DAY(A1) 1)
gt;
gt; 1 year, 1 month
gt; =DATE(YEAR(A1) 1,MONTH(A1) 1,DAY(A1))
gt;
gt; First day of following month
gt; =DATE(YEAR(A1),MONTH(A1) 1,1)
gt;
gt; Last day of current month
gt; =DATE(YEAR(A1),MONTH(A1) 1,0)
gt;
gt; Last day of following month
gt; =DATE(YEAR(A1),MONTH(A1) 1 1,0)
gt;
gt; Tenth day of following month
gt; =DATE(YEAR(A1),MONTH(A1) 1,10)
gt;
gt; Bill Ridgeway
gt; Computer Solutions
gt;gt;
gt;gt;
gt;gt;
gt;
gt;