Re: Manipulating dates (was Add 6 months to a date)

[Bill Ridgeway]

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
>
>
>

[CLR]

Very handy compilation Bill..........another one for my secret stash.

Thanks for posting it.

Vaya con Dios,
Chuck, CABGx3




"Bill Ridgeway" wrote:

> 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
> >
> >
> >
>
>
>

[Ken Wright]

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

http://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 :-)
------------------------------*------------------------------*----------------


"Bill Ridgeway" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>>
>>
>
>