Automatic date spans in Excel

Posted on 2013-09-19
Medium Priority
Last Modified: 2013-09-19
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.
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?

Author Comment

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

Expert Comment

by:barry houdini
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
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

LVL 54

Accepted Solution

Rgonzo1971 earned 2000 total points
ID: 39506171

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



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))
LVL 50

Expert Comment

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

In A2.....


and this one in A3


regards, barry
LVL 35

Expert Comment

by:Rob Henson
ID: 39506191
Start date:


Finish Date:


In xl03 EOMONTH needs AnalysisToolpak AddIn enabled.

Rob H

Author Closing Comment

ID: 39506233
Thank you very much!  This worked perfectly!
LVL 50

Expert Comment

by:barry houdini
ID: 39506326

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:


regards, barry

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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.

Join & Write a Comment

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

586 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question