Financial rear date ranges in a single cell

[Glen]

Hello,

I do a lot of work with financial years that run from mid-year to mid-year
(eg 1 Jul '08 to 30 Jun '09). I think this is more a feature request but
feel free to read on...

Has anyone come up with an elegant way to represent a date range within a
single cell that can then be used in formula without having to deconstruct
the cell to extract the date range back out again?

Two inelegant options are using two columns to represent the start and end
of the date period, or using a single column but then having to calculate a
date offset in every formula that sums/counts/averages data back to the
period.

Wouldn't it be nice if you could just enter 1/7/08:30/06/09 into a cell and
have the value of that cell be that date range?

Looking forward to that elusive elegant solution (sigh)

Cheers,

Glen

 

[Ron@Buy]

Curious request !!
"the value of that cell be that date range?"
What do you expect the value to be?
One date multiplied by the other - one subtracted from the other or maybe
some other function ?
Why is using two columns an inelegant solution - it makes it so easy to
incorporate or change each date - you can get rid of the visual dividing grid
line by changing the fill colour to white!
After having said that; is incorporating something like
=DATEVALUE(LEFT(A1,8)) and/or =DATEVALUE(RIGHT(A1,8)) in your formulae such a
difficult option?
I'm still curious !!

 

[Glen]

What I’m trying to achieve is a cell with a value that means the range of
time between a start and finish. So 1/1/2008:1/1/2009 would mean the whole
of 2008. At the moment, a cell with a date time value can only refer to a
single moment. There’s no way to have a cell with the value of July 2009
that means the whole month. The closest is 1/7/2009, which actually means
12:00am 1/7/2009.

Cells with an array formula can represent a series of discrete values (eg
1,2,3), which is kind of close, but what about a cell with a value which is a
continuous range?

Why I’m asking is because I’m building reports on a number of different
activities, some of which are annual, some quarterly, monthly, weekly etc.
Aggregating the data up (eg counting, summing) into these periods would be
simpler if there was a way to filter for something like ‘in’ or ‘between’ and
compare it with a date range. Also, column headings (and then graphing)
become imprecise, with point values representing unspecified larger time
ranges. I’ve just finished an exercise where periodic meter readings were
prorated to the start and end of each month and the difference was the usage
for the month. But the month’s usage can only be labelled with a date for a
particular day. And then I roll it up to six monthly periods and try and
remember which dates fit under which 6 months! Alternatively, in another
application, as soon as I head something with “2008/09†to represent the
’08-‘09 financial year, it becomes text and is of no use for
sorting/calculating further with respect to further aggregation, lookups etc.

As I’ve thought about this more, I’ve realised that I’d still end up needing
two cells to calculate the start and end values of the range using formula,
so not much would be gained there anyway. However, for subsequent
calculations, it still think it would be of use.

Cheers,

Glen