Working with Dates

[voisey]

I have a spreadsheet that contains many records. One field is the date. This field is formatted to show DDMMMYY.

I need to calculate ‘this months’ figures based on the date, so I have inserted a ‘helper’ column next to it which copies the adjoining cell, but is formatted to show =MONTH() which returns an integer to indicated the month. However, the spreadsheet covers more than twelve months and therefore I find that when calculations are made it includes figures for last April 05 (month 1) as well as April 06 (month 1). I’ve tried to format this helper column/cell as MMMYY but when I try to calculate it does not work – Excel still reads the helper cell as the specific date.

Is there a way to get Excel to add up all the APR 06 figures

We need to retain the actual day as an entry. I know that we could also manually input into the helper cell MMMYY but this would seem to be an extra entry that should be able to be automated.

Any help appreciated

Voisey

 

[GlennUK]

Have the helper column use =TEXT("MMMYY") instead, and you can use the string APR06 inside a SUMIF to get the data you want. Or use a PivotTable. Or use a SUMPRODUCT formula that tests for month of date column.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[SkipVought]

Hi,

FYI, Why do Dates and Times seem to be so much trouble? faq68-5827

Also, try entering the starting date and ending date of your range. Often this is a column of dates, one week, one month or one year apart.

Assuming that the date range is in column A and you're calculating values in column B and you have 10 rows of data and your two dates are in AA1 & AA2 ...
[tt]
=sumproduct(($A$1:$A$10=AA1)*($B$1:$B$10))
[/tt]
No need for a helper column.

BTW, you can also use the Pivot Table Wizard and GROUP your date column by Month & Year. This would be ALOT faster than the above suggestion. CAVEAT: EVERY cell in the Date Range MUST HAVE A VALID DATE.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}