Here is my problem...I need to calculate the age (using VBA) of an item,
however when my spreadsheet refreshes (importing data via web query) there
are numerous date formats depending on when a user modified, closed, or
adjusted the particular item. If the item is "acted on" (i.e. opened,
closed, modified, etc.) in the seven days prior to my report then the date
appears as DayofWeek Time (i.e. Wed 15:15). To complicate matters further,
if the item is "acted on" during the day that I run my report then the date
column contains the time of the action on the item (i.e. 7:16). The only
other variable in this problem is "Status"...if the item is "Closed", I
determine the age by calculating the difference between Column B then Column
C, if it is not "Closed", then the age is determined by calculating the
difference between Column B and now().

Examples:
A B C
D E

Item Date Opened Date Closed/Changed Age of Item Status
1 01/01/08 02/01/08 30
Closed
2 08/06/08 Wed 15:15 ?
3 02/01/08 16:11 ?

When I made the original post, the examples that I included at the bottom
obviously did not format the way I had intended. I'll try it another way....

Column A=Item Number
Column B=Date Item Opened
Column C=Date Item was Closed/Changed
Column D=Calculated Age of Item

When Columns B and C both contain dates...No problem.

When one of these two columns were adjusted within the previous seven
days...No date appears, instead it populates with DayofWeek TimeofDay (i.e.
Wed 15:15).

If an action was performed on an item on the day that the report is
run...only the time of the action appears in the column (i.e. 16:11). When
formatted as date and time, 16:11 becomes 1/0/1900 16:11.

Many thanks for any help, direction, or thoughts on this problem.

P


"PJH" wrote:

Here is my problem...I need to calculate the age (using VBA) of an item,
however when my spreadsheet refreshes (importing data via web query) there
are numerous date formats depending on when a user modified, closed, or
adjusted the particular item. If the item is "acted on" (i.e. opened,
closed, modified, etc.) in the seven days prior to my report then the date
appears as DayofWeek Time (i.e. Wed 15:15). To complicate matters further,
if the item is "acted on" during the day that I run my report then the date
column contains the time of the action on the item (i.e. 7:16). The only
other variable in this problem is "Status"...if the item is "Closed", I
determine the age by calculating the difference between Column B then Column
C, if it is not "Closed", then the age is determined by calculating the
difference between Column B and now().

Examples:
A B C
D E

Item Date Opened Date Closed/Changed Age of Item Status
1 01/01/08 02/01/08 30
Closed
2 08/06/08 Wed 15:15 ?
3 02/01/08 16:11 ?