Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
any way to tell the last day of the month? when the month is a var?
ie, if a a1 has 10/9/6 as a date, then month will give me the numeric month into say a2, need to now how to find 31 into a3 or " 4/6/6 " 30 " thanks. jim |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
any way to tell the last day of the month? when the month is a var
=DATE(YEAR(A1),MONTH(A1)+1,0) will give last day of month HTH "jim sturtz" wrote: ie, if a a1 has 10/9/6 as a date, then month will give me the numeric month into say a2, need to now how to find 31 into a3 or " 4/6/6 " 30 " thanks. jim |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
any way to tell the last day of the month? when the month is a var
Several ways
=DAY(DATE(YEAR(A1),MONTH(A1)+1,0)) or with Analysis Toolpak =DAY(EOMONTH(A1,0)) or my favorite for unintelligibility! =DAY(A1-DAY(A1)+32-DAY(A1-DAY(A1)+32)) "jim sturtz" wrote: ie, if a a1 has 10/9/6 as a date, then month will give me the numeric month into say a2, need to now how to find 31 into a3 or " 4/6/6 " 30 " thanks. jim |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
any way to tell the last day of the month? when the month is a var?
On Mon, 9 Oct 2006 11:31:47 -0400, "jim sturtz" wrote:
ie, if a a1 has 10/9/6 as a date, then month will give me the numeric month into say a2, need to now how to find 31 into a3 or " 4/6/6 " 30 " thanks. jim =A1-DAY(A1)+32-DAY(A1-DAY(A1)+32) will give the date of the last day of the month that is in A1. If you want only the day number, then: =DAY(A1-DAY(A1)+32-DAY(A1-DAY(A1)+32)) or for the date: =DATE(YEAR(A1),MONTH(A1)+1,0) for just the day: =DAY(DATE(YEAR(A1),MONTH(A1)+1,0)) --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
any way to tell the last day of the month? when the month is a var?
thanks alot for the quick replies
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a rolling 6 month average? | Excel Discussion (Misc queries) | |||
recognizing dates past End of Month | Excel Discussion (Misc queries) | |||
Working days left in the month compared to previous months | Excel Worksheet Functions | |||
formula: First and last day in month | Excel Worksheet Functions | |||
Month Year Date Format | Excel Worksheet Functions |