| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date if before or after 15th of month
Hello-
My due date should be the 1st of the month if the start date is after the 15th or the 1st of the previous month if the start date is before the 15th, but only if there is an amt in B7 otherwise blank. B2=Start Date B7=Amt C7=Due Date |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date if before or after 15th of month
You did not indicate which way to go if the start date is the 15th. This
formula placed in cell C7 assumes that is the start date is the 15 it will be due the following month: =IF(ISBLANK(B7),"",IF(DAY(B2)<15,DATE(YEAR(B2),MON TH(B2),1),DATE(YEAR(B2),MONTH(B2)+1,1))) Pay them bills! "wf315" wrote: Hello- My due date should be the 1st of the month if the start date is after the 15th or the 1st of the previous month if the start date is before the 15th, but only if there is an amt in B7 otherwise blank. B2=Start Date B7=Amt C7=Due Date |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date if before or after 15th of month
Thanks Tom - I meant to say on or before the 15th. How do I enter it? You've been a great help and time saver :) "TomPl" wrote: You did not indicate which way to go if the start date is the 15th. This formula placed in cell C7 assumes that is the start date is the 15 it will be due the following month: =IF(ISBLANK(B7),"",IF(DAY(B2)<15,DATE(YEAR(B2),MON TH(B2),1),DATE(YEAR(B2),MONTH(B2)+1,1))) Pay them bills! "wf315" wrote: Hello- My due date should be the 1st of the month if the start date is after the 15th or the 1st of the previous month if the start date is before the 15th, but only if there is an amt in B7 otherwise blank. B2=Start Date B7=Amt C7=Due Date |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date if before or after 15th of month
Try this:
=IF(B7="","",EOMONTH(B2,(DAY(B2)15)-1)+1) It required ATP add-ins "wf315" wrote: Thanks Tom - I meant to say on or before the 15th. How do I enter it? You've been a great help and time saver :) "TomPl" wrote: You did not indicate which way to go if the start date is the 15th. This formula placed in cell C7 assumes that is the start date is the 15 it will be due the following month: =IF(ISBLANK(B7),"",IF(DAY(B2)<15,DATE(YEAR(B2),MON TH(B2),1),DATE(YEAR(B2),MONTH(B2)+1,1))) Pay them bills! "wf315" wrote: Hello- My due date should be the 1st of the month if the start date is after the 15th or the 1st of the previous month if the start date is before the 15th, but only if there is an amt in B7 otherwise blank. B2=Start Date B7=Amt C7=Due Date |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date if before or after 15th of month
In the formula I posted just change < 15 to <16.
Hope that helps "wf315" wrote: Thanks Tom - I meant to say on or before the 15th. How do I enter it? You've been a great help and time saver :) "TomPl" wrote: You did not indicate which way to go if the start date is the 15th. This formula placed in cell C7 assumes that is the start date is the 15 it will be due the following month: =IF(ISBLANK(B7),"",IF(DAY(B2)<15,DATE(YEAR(B2),MON TH(B2),1),DATE(YEAR(B2),MONTH(B2)+1,1))) Pay them bills! "wf315" wrote: Hello- My due date should be the 1st of the month if the start date is after the 15th or the 1st of the previous month if the start date is before the 15th, but only if there is an amt in B7 otherwise blank. B2=Start Date B7=Amt C7=Due Date |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formatting for 15th and last day of the month | Excel Worksheet Functions | |||
| date formula returns the 15th or the end of month | Excel Worksheet Functions | |||
| If date in column A is less than 15th of month can I display next. | Excel Discussion (Misc queries) | |||
| 15th of prior month | Excel Worksheet Functions | |||
| Date arithmetic: adding 1 month to prior end of month date | Excel Worksheet Functions | |||
Linear Mode
