Solved

# Automatic date spans in Excel

Posted on 2013-09-19
Medium Priority
311 Views
I am in need of a formula that if the current date is between the 1st and 15th of the month the date span will be the 16th through the end of the previous month.  If the current date is between the 16th and the end of the month, then the date span will be the 1st to the 15th of the current month.

Thank you in advance for any help.
0
Question by:jmkbrown
• 3
• 2
• 2
• +2

LVL 22

Expert Comment

ID: 39506154
Can you explain what you mean by "date span"?  Normally cells only show individual values.  Are you looking for all dates in adjacent cells?
For example, if A1 = 2/1/2013, then A2=1/16, A3=1/17, A4=1/18, etc?
0

Author Comment

ID: 39506161
Sorry for example
A1 = 09/19/2013 then
A2 = 09/01/2013
A3 = 09/15/2013
0

LVL 50

Expert Comment

ID: 39506168
How do you want to return the "date span"? As a text value you can try this

=IF(DAY(TODAY())<16,"16-"&TEXT(EOMONTH(TODAY(),-1),"d mmm yyyy"),"1-15 "&TEXT(EOMONTH(TODAY(),-1)+1,"mmm yyyy"))

Today that will give you this:

1 - 15 Sep 2013

but on 1st October you'll get

16 - 30 Sep 2013

regards, barry
0

LVL 54

Accepted Solution

Rgonzo1971 earned 2000 total points
ID: 39506171
Hi,

pls try =IF(DAY(E5)<=15,EOMONTH(E5,-2)+16,EOMONTH(E5,-1)+1)

and

=IF(DAY(E5)<=15,EOMONTH(E5,-1)-1,EOMONTH(E5,-1)+15)

Regards
0

LVL 22

Expert Comment

ID: 39506185
This formula can give you the 1st of the current month or 16th of the previous month for any given date:

=IF(DAY(A1)<16, DATE(YEAR(A1-15),MONTH(A1-15),16),DATE(YEAR(A1),MONTH(A1),1))
0

LVL 50

Expert Comment

ID: 39506190
Sorry, I thought you wanted text.....if you have today's date in A1 try this:

In A2.....

=IF(DAY(A1)>15,A1-DAY(A1)+1,EOMONTH(A1,-2)+16)

and this one in A3

=IF(DAY(A2)=1,A2+14,EOMONTH(A2,0))

regards, barry
0

LVL 35

Expert Comment

ID: 39506191
Start date:

=IF(DAY(TODAY())<16,EOMONTH(TODAY(),-2)+16,EOMONTH(TODAY(),-1)+1)

Finish Date:

=IF(DAY(TODAY())<16,EOMONTH(TODAY(),-1),EOMONTH(TODAY(),-1)+15)

In xl03 EOMONTH needs AnalysisToolpak AddIn enabled.

Thanks
Rob H
0

Author Closing Comment

ID: 39506233
Thank you very much!  This worked perfectly!
0

LVL 50

Expert Comment

ID: 39506326
>=IF(DAY(E5)<=15,EOMONTH(E5,-1)-1,EOMONTH(E5,-1)+15)

If DAY(E5)<=15 that will give you the day before the end of the month, the -1 after the first EOMONTH isn't required, should be:

=IF(DAY(E5)<=15,EOMONTH(E5,-1),EOMONTH(E5,-1)+15)

regards, barry
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.