Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
roundup a month
If a cell returns a date between the 2nd and last day of a given month. What
worksheet function can be used to return the 1st day of the following month? -- JR |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
roundup a month
If the function is in A1 then use this in B1
=DATE(YEAR(A1),MONTH(A1)+1,1) or you can replace A1 with the formula you have returning a date between 2nd and last day of the month... ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "JR" wrote: If a cell returns a date between the 2nd and last day of a given month. What worksheet function can be used to return the 1st day of the following month? -- JR |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
roundup a month
Assuming your cell is A1...
=DATE(YEAR(A1),MONTH(A1)+(DAY(A1)1),1) -- Rick (MVP - Excel) "JR" wrote in message ... If a cell returns a date between the 2nd and last day of a given month. What worksheet function can be used to return the 1st day of the following month? -- JR |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
roundup a month
Thank you for responding. This works great!
-- JR "Sheeloo" wrote: If the function is in A1 then use this in B1 =DATE(YEAR(A1),MONTH(A1)+1,1) or you can replace A1 with the formula you have returning a date between 2nd and last day of the month... ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "JR" wrote: If a cell returns a date between the 2nd and last day of a given month. What worksheet function can be used to return the 1st day of the following month? -- JR |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
roundup a month
Thanks for your answer. In comparing the two responses you have added
+(DAY(A1)1),1). I was just wondering why the difference? -- JR "Rick Rothstein" wrote: Assuming your cell is A1... =DATE(YEAR(A1),MONTH(A1)+(DAY(A1)1),1) -- Rick (MVP - Excel) "JR" wrote in message ... If a cell returns a date between the 2nd and last day of a given month. What worksheet function can be used to return the 1st day of the following month? -- JR |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
roundup a month
Unless I read your question incorrectly, you seemed to say you wanted the
formula to return the first of the next month ONLY for day 2 through the end of the month which I took to mean if the date was the first of the month, the date shouldn't change. The formula I posted does that. The formula Sheeloo posted will advance the first of the month to the month as well as from day 2 on unless you house it in an IF function call that looks specifically for that condition... the "added" part of my formula takes care of that test automatically, so my formula can be used exactly as posted (again, assuming I understood your question correctly). -- Rick (MVP - Excel) "JR" wrote in message ... Thanks for your answer. In comparing the two responses you have added +(DAY(A1)1),1). I was just wondering why the difference? -- JR "Rick Rothstein" wrote: Assuming your cell is A1... =DATE(YEAR(A1),MONTH(A1)+(DAY(A1)1),1) -- Rick (MVP - Excel) "JR" wrote in message ... If a cell returns a date between the 2nd and last day of a given month. What worksheet function can be used to return the 1st day of the following month? -- JR |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
roundup a month
Yes, you understood the question and you are correct in adding the
(DAY(A1)1),1) otherwise is the first formula changes the month not considering if it is the 1st or 2nd. thank you -- JR "Rick Rothstein" wrote: Unless I read your question incorrectly, you seemed to say you wanted the formula to return the first of the next month ONLY for day 2 through the end of the month which I took to mean if the date was the first of the month, the date shouldn't change. The formula I posted does that. The formula Sheeloo posted will advance the first of the month to the month as well as from day 2 on unless you house it in an IF function call that looks specifically for that condition... the "added" part of my formula takes care of that test automatically, so my formula can be used exactly as posted (again, assuming I understood your question correctly). -- Rick (MVP - Excel) "JR" wrote in message ... Thanks for your answer. In comparing the two responses you have added +(DAY(A1)1),1). I was just wondering why the difference? -- JR "Rick Rothstein" wrote: Assuming your cell is A1... =DATE(YEAR(A1),MONTH(A1)+(DAY(A1)1),1) -- Rick (MVP - Excel) "JR" wrote in message ... If a cell returns a date between the 2nd and last day of a given month. What worksheet function can be used to return the 1st day of the following month? -- JR |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can't roundup sum | Excel Worksheet Functions | |||
Roundup in VBA | Excel Discussion (Misc queries) | |||
Roundup | Excel Discussion (Misc queries) | |||
ROUNDUP and -1 | Excel Worksheet Functions | |||
How can I roundup to next 0 i.e. 71 to 80? | Excel Worksheet Functions |