If date is between 1 through 1th of month return value

**sclasen24** · 07-31-2014, 04:10 PM

If I make a sale between the 1st of each month through the 1th of each month I will be paid on the 31st of that month. If I make a sale on the 16th of each month through to the last day of each month I will be paid on the 15th of the next month. I need this formula to work for every month.

cell e has date of sale
cell h has the returned value of the formula above

**simarui** · 07-31-2014, 04:16 PM

hi sclasen,

This post is a bit difficult to understand. It would help if you uploaded a sample.

I think the answer you want though is:

Formula:

=if(day(e1)<=15,eomonth(e1,0),eomonth(e1,0)+15)

(untested) if that doesn't work, upload a sample workbook.

**Tony Valko** · 07-31-2014, 04:21 PM

Try this...

Data Range

	A	B
1	Date	Pay Date
2	7/11/2014	7/31/2014
3	7/19/2014	8/15/2014
4	7/28/2014	8/15/2014
5	7/30/2014	8/15/2014
6	7/21/2014	8/15/2014
7	7/14/2014	7/31/2014
8	7/7/2014	7/31/2014
9	7/28/2014	8/15/2014
10	7/12/2014	7/31/2014

This formula entered in B2 and copied down:

=IF(DAY(A2)<16,EOMONTH(A2,0),EOMONTH(A2,0)+15)

Format as Date.

If date is between 1 through 1th of month return value

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

If date is between 1 through 1th of month return value

Re: If date is between 1 through 1th of month return value

Re: If date is between 1 through 1th of month return value

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Formula to return End OF Month date with non-calendar month lengths.

If date is in future return no value, otherwise return in a new cell the month i.e june=6

[SOLVED] lookup date within date range grid to return fiscal month value

Formula to return next month end date after a month end date.

Formula to return day based on month end date & day number of month

Bookmarks

Bookmarks

Posting Permissions