Solved

Begin and End Dates

Posted on 2011-03-09
12
227 Views
Last Modified: 2012-05-11
Hi:

Attached is an Excel spreadsheet with a column of dates.

I need a formula for the “Begin Period”, similar to the one I have in the spreadsheet for the “End Period”. The “Begin Period” is based on the column of dates and is at the bottom of the column, but that column varies with number of dates and rows each time the report is run.  

I need the result in the format, for example, "December, 2010"

Keep in mind the column of dates will always be a different number of rows. In one reporting period there may be 50 rows of dates, in others it may be 200 rows of dates. The first row will always be fixed but the last row, obviously, will always be different based on the varying number of dates.

Currently, for the "End Period" formula I have, and it works:

=TEXT(DATEVALUE(LEFT(A4,3)&" " &MID(A4,FIND(" @",A4)-4,4)),"mmmm, yyyy")

I just need the Begin Period formula for the month & year, but am not sure how to do this with varying numbers of rows of dates.

Thank you!

 Dates-2.xlsx
0
Comment
Question by:Cactus1994
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 35087318
Here is an array formula for that, to be entered with Ctrl+Shift+Enter
=INDEX(A:A,MAX(IF(LEN(A:A)>0,ROW(A:A),0)))

Thomas
0
 

Author Comment

by:Cactus1994
ID: 35087372
Nutsch:

I'd rather not use arrays, due to the fact the number of rows of dates will constantly be changing. Is there a way to do this without using an array?  Thanks.
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35087387
>due to the fact the number of rows of dates will constantly be changing

why is that an issue with array formulas since you're looking at the whole column anyway?

Thomas
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Cactus1994
ID: 35087415
... plus, it only returns #VALUE! when entered into cell B1 on the example spreadsheet and adjusted to begin at cell A4 (the beginning of the column of dates.)
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35087489
I insist on
=TEXT(DATEVALUE(LEFT(INDEX(A:A,MAX(IF(LEN(A:A)>0,ROW(A:A),0))),12)),"MMMM, YYY")

as in your attached, updated sample file.

What part doesn't fit?

T


Copy-of-Dates-2.xlsx
0
 
LVL 39

Expert Comment

by:nutsch
ID: 35087519
Warning, the formula will return #VALUE if it's validated as a standard formula, ie with Enter. You need to enter the cell (using F2) and validate with Ctrl+Shift+Enter to have it work as an array formula. When you do this, you will see {} appear around the formula in the formula box.

Thomas
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35087596
Assuming that you only have those dates in column A then you can use this non-array version

=TEXT(LEFT(LOOKUP("zzz",A:A),12),"mmmm, yyyy")

regards, barry
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35087615
...similarly you can simplify the end date formula to just

=TEXT(LEFT(A4,12),"mmmm, yyyy")

regards, barry
0
 

Author Comment

by:Cactus1994
ID: 35087716
Nutsh and Barry:

The array formula Nutsch offered did in fact work ... I had to change a bit of the formula as my example sheet was just a subset of my larger spreadsheet. I would like to use non-array formulas, simply because I'm not familiar with arrays and was under the impression I had to change them often when the number of cells, rows, columns etc changed. Using the Begin Date, non-array formula in my larger spreadsheet, I got FALSE as a return, but the simplified end date formula did work. Am I missing something with the Begin Date? Is "zzz" exactly what I should enter?  In my larger spreadsheet, all the dates begin in cell B9 and run down the column, with no other type of entries within the column.Again, the number of rows and dates varies, but nothing else but those dates are entered from cell B9 down. Thanks.
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 35088160
As your "dates" are actually text the LOOKUP formula is appropriate because it should find the last text value in a column, so for your last date in column B you can use

=LOOKUP("zzz",B:B)

Exactly like that - does that give you the last date in column B?

Then you can extract the first 12 characters using

=LEFT(LOOKUP("zzz",B:B),12)

and finally TEXT function gives you the format you want, i.e.

=TEXT(LEFT(LOOKUP("zzz",B:B),12),"mmmm, yyyy")

That works for me in your sample file - applied to column A and with other data removed

regards, barry

0
 
LVL 39

Expert Comment

by:nutsch
ID: 35088395
barry's solution worked for me too.

T
0
 

Author Closing Comment

by:Cactus1994
ID: 35088473
Guys -- Thank you so much. Works perfectly. I had copied the formula incorrectly and that was apparently the problem. Nutsch, thank you so much for your help, but with my unfamiliarity with arrays I'm more comfortable with Barry's non-array formula, and thank you Barry for explaining it in detail as you did.

Have a great week, and thanks to you both again!!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

739 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