| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Quarterly inputs
How do i input the last day of the current quarter into cell B1? However, if
the date within cell A1 is within the current quarter then enter the last day of the next quarter into cell B1. If the date in cell A1 is not at a minumum of the previous quarter (two quarters ago), then I require a conditional format to turn cell A1 red. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Quarterly inputs
1. Last day of current quarter in cell B1:
=DATE(YEAR(TODAY()),3*ROUNDUP(MONTH(TODAY())/3,0)+1,1)-1 2. Last day unless A1 is in the current qtr then next qtr last day: =IF(ROUNDUP(MONTH(A1)/3,0)=ROUNDUP(MONTH(TODAY())/3,0),EDATE(DATE(YEAR(TODAY()),3*ROUNDUP(MONTH(TODA Y())/3,0)+1,1),3)-1,DATE(YEAR(TODAY()),3*ROUNDUP(MONTH(TODAY())/3,0)+1,1)-1) "Loadmaster" wrote: How do i input the last day of the current quarter into cell B1? However, if the date within cell A1 is within the current quarter then enter the last day of the next quarter into cell B1. If the date in cell A1 is not at a minumum of the previous quarter (two quarters ago), then I require a conditional format to turn cell A1 red. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Quarterly inputs
Thank-you for the formula. I think I already have the conditional formattting
at work. "Sam Wilson" wrote: 1. Last day of current quarter in cell B1: =DATE(YEAR(TODAY()),3*ROUNDUP(MONTH(TODAY())/3,0)+1,1)-1 2. Last day unless A1 is in the current qtr then next qtr last day: =IF(ROUNDUP(MONTH(A1)/3,0)=ROUNDUP(MONTH(TODAY())/3,0),EDATE(DATE(YEAR(TODAY()),3*ROUNDUP(MONTH(TODA Y())/3,0)+1,1),3)-1,DATE(YEAR(TODAY()),3*ROUNDUP(MONTH(TODAY())/3,0)+1,1)-1) "Loadmaster" wrote: How do i input the last day of the current quarter into cell B1? However, if the date within cell A1 is within the current quarter then enter the last day of the next quarter into cell B1. If the date in cell A1 is not at a minumum of the previous quarter (two quarters ago), then I require a conditional format to turn cell A1 red. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to calculate quarterly | New Users to Excel | |||
| Quarterly tax percentages | Excel Worksheet Functions | |||
| How do I set up a quarterly calendar? | New Users to Excel | |||
| quarterly totals | Charts and Charting in Excel | |||
| quarterly reports | Excel Discussion (Misc queries) | |||
Linear Mode
