H
Heyna
Charts/ Graphs and Dates Trouble
--------------------------------------------------------------------------------
Hi Folks;
I'm working on a spreadsheet where I have the need to add graphs from
live information in the spreadsheet. This spreadsheet is being asked to
stand alone as a Template that any Job Document can be imported into,
and various data will fill into a "Project At A Glimpse" page that will
drive form other cells on other pages, and fill in cells involved in
tables to create about 6 charts/graphs. I've dutifully made tables for
the information needed, but the problem I have is finding a way to
"stabilize" the date information and then get it to graph for me.
I'll try to inform this question with a legitimate example. The
spreadsheet is used to track schedule and costs for a construction job.
Some of the information that is being tracked is simple enough, such as
"Original Start Date" , "Actual Start Date" and "Projected Start Date".
These dates are filled in by others in a separate spreadsheet in text
style (dd/mm/yyyy). There is data that "my" part of the spreadsheet
gets from these values, such as "Estimated Time Left" and "Projected
Completion Time" and the all-important "Days Late on Payment" that are
to show as numbers of days. I used the DAYS360 function to find the
value for these needs.
Here's the problem: I want to be able to find a way to make these
different types of date data, serial number and text, show up as one
type of data that can be used to put together a graph or chart. The
problem, of course, is that DAYS360, although a serial number, is a way
to figure out days in between two dates :i.e. 175 days, so if you change
everything to DATEVALUE, the DAYS360 stuff will show for some time in
1901, thanks to that Excel/ Microsoft peculiarity. Is there someting I
can do, in early stages, to overcome using text and serial , with the
problems of how days (today) show like 38350 in the DATEVALUE deal, and
how, if you have a set of values in that serial format, you can get a
graph to show them as text dates? I still have the need to show, in the
data labels of graphed info, the days (DAYS360) for easy reading.
Thanks so much, and look forward to your input...
Scott
--------------------------------------------------------------------------------
Hi Folks;
I'm working on a spreadsheet where I have the need to add graphs from
live information in the spreadsheet. This spreadsheet is being asked to
stand alone as a Template that any Job Document can be imported into,
and various data will fill into a "Project At A Glimpse" page that will
drive form other cells on other pages, and fill in cells involved in
tables to create about 6 charts/graphs. I've dutifully made tables for
the information needed, but the problem I have is finding a way to
"stabilize" the date information and then get it to graph for me.
I'll try to inform this question with a legitimate example. The
spreadsheet is used to track schedule and costs for a construction job.
Some of the information that is being tracked is simple enough, such as
"Original Start Date" , "Actual Start Date" and "Projected Start Date".
These dates are filled in by others in a separate spreadsheet in text
style (dd/mm/yyyy). There is data that "my" part of the spreadsheet
gets from these values, such as "Estimated Time Left" and "Projected
Completion Time" and the all-important "Days Late on Payment" that are
to show as numbers of days. I used the DAYS360 function to find the
value for these needs.
Here's the problem: I want to be able to find a way to make these
different types of date data, serial number and text, show up as one
type of data that can be used to put together a graph or chart. The
problem, of course, is that DAYS360, although a serial number, is a way
to figure out days in between two dates :i.e. 175 days, so if you change
everything to DATEVALUE, the DAYS360 stuff will show for some time in
1901, thanks to that Excel/ Microsoft peculiarity. Is there someting I
can do, in early stages, to overcome using text and serial , with the
problems of how days (today) show like 38350 in the DATEVALUE deal, and
how, if you have a set of values in that serial format, you can get a
graph to show them as text dates? I still have the need to show, in the
data labels of graphed info, the days (DAYS360) for easy reading.
Thanks so much, and look forward to your input...
Scott