Excel - Calc Days of Month, end at end of month

[saw15]

I am new to excel, and creating a worksheet, each tab/sheet will have a month view on it. I want to have the user enter the first day of month at top "1/1/2007" and then prepopulate the rest of days ending at the end of the month.

Currently I am using =a1 + 1 etc, but know there has got to be something a little more powerful.

I appreciate any help you can provide, thanks in advance.

 

[xlhelp]

Manually, what you are doing is probably the acceptable way, however, you might want to do it programatically; in that case, please post in Forum 707 VB for Apps

Member- AAAA Association Against Acronym Abusers

 

[bluedragon2]

put 1/1/2007 in cell A1 then click on the lower right corner of the cell and drag down.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[xlbo]

why store your data in month tabs?

If you store ALL the data on one sheet, you will find it much easier to do any reporting. I cannot think of a single good reason to store the data on seperate sheets. Please note that the storage of data is not the same as the reporting of data. This can be done seperately and if you store the data all in one place, much, much more easily

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bluedragon2]

Wholeheartedly agree with xlbo

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[xlhelp]

Mr B,

I am going use only half of my heart to agree with you that whenever possible it is better to have all your data in one place.

But, when your managers want to see monthly activity and they want to compare month-to-month, it makes more sense to have monthly sheets than to have all your data in one place and parse it just for manager's pleasure. In my case, I combine my data to get my YTD results that I want to see.

Just my tuppence (I gotta use the Brit lingo)

Member- AAAA Association Against Acronym Abusers

 

[xlbo]

Mr H,
whilst I can see where you are coming from, I have to disagree. If the requirement is to view month on month data then the report can be configured to show that as well as any other number of things. If the data is held seperately, it is still harder (IMO) to show month on month reporting than if it were stored in one place.

It comes down to 1 thing:

Data storage is not data reporting.

The 2 are seperate and should be kept that way. This kind of request always has the same motivation - the user wants to store data in the same way that they report on it.

I think I can say without too much fear of contradiction that all of excel's reporting tools work best on 1 single data source which should tell us all a lot about the best way to store data (if you want to report on it in excel)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

I am absolutely, 100% with Geoff on this.

My argument for folks who want to store data in little bits is, what happens when the manager asks to see quarterly data on the fly? Or the last two months blended? Or Monday data vs. Thursday data for the last 6 months? Or year-over-year data for the last four years?

"Um, that will take me a few minutes (hours) to compile for you because I need to stitch together all the data...."

OR

You can store your data in a single table and use built in functions to slice and dice it any way you want within seconds.

Another weakness of chopping up your data: As time goes on you're likely to add or remove fields (columns) to the sheets. Now your data isn't apples-to-apples anymore and you're in for a world of pain when that inevitable request for quarterly data comes your way.

saw15: I know where you're coming from. When I was new to Excel I did the same thing with some of my data (storing it by week or month on different sheets). Then a year or so later I had to take the time and trouble to combine all the data into a single sheet. Save yourself now, while you can.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

Let me add to the chorus. Best practices and experienced users put similar data in one table (sheet)

From that table you will be able to report infromation related to a day, a week, a month, a quarter, a year, 2 months -- the possibilities are nearly endless.

Use
Data>
PivotTable, or
AutoFilter, or
Subtotal, or
Get External Data

Skip,

_[red][/red]