Time and Date Formula Giving Strange Result

[EfficientOne]

Hello,
I have researched archived threads, the web, and Excel help and I'm tired and confused.

Columns -
1: Open Date & Time (format mm/dd/yyyy hh:mm)
2: Last Edited/Time State Changed (format mm/dd/yyyy hh:mm)
3: Days Since Ticket Last Changed =DATEDIF(P2,TODAY(),"d")
4: Weeks Since Ticket Last Changed =INT(S2/7)

Columns 3 & 4 have been formatted in General, Number, and Text.

The problem arises when Column 2 is blank.

1: 11/02/2011 10:27
2: Blank
3: 40919
4: 5845

What do I need to do and in which column to ensure it gives me something other than these numbers?

The results of all of the data is placed in a pivot table grouped by 4 week intervals and it looks very strange to have weeks 5844-5847.

Thanks for your help.

P.S. I found the formulas for 3 and 4 on an archived forum so for people like me who don't ask for help until they absolutely have to, thank you to all of you whom impart your knowledge.

 

[EfficientOne]

Hello,
I have researched archived threads, the web, and Excel help and I'm tired and confused.

Columns -
1: Open Date & Time (format mm/dd/yyyy hh:mm)
2: Last Edited/Time State Changed (format mm/dd/yyyy hh:mm)
3: Days Since Ticket Last Changed =DATEDIF(P2,TODAY(),"d")
4: Weeks Since Ticket Last Changed =INT(S2/7)

Columns 3 & 4 have been formatted in General, Number, and Text.

The problem arises when Column 2 is blank.

1: 11/02/2011 10:27
2: Blank
3: 40919
4: 5845

What do I need to do and in which column to ensure it gives me something other than these numbers?

The results of all of the data is placed in a pivot table grouped by 4 week intervals and it looks very strange to have weeks 5844-5847.

Thanks for your help.

P.S. I found the formulas for 3 and 4 on an archived forum so for people like me who don't ask for help until they absolutely have to, thank you to all of you whom impart your knowledge.

I forgot to add that I am using Excel 2007

The results that I am looking for is something like 'No Information' or blank. That way the pivot table would make a little more sense to the executives and managers reviewing the pivot graph and pivot table. 5845 makes it appear it hasn't been looked at in 112 years! (5845/52)

 

[barry houdini]

If P2 is blank then Excel treats the date as 1/1/1900 and so the result you get is the days since then. You can check whether P2 is blank, i.e.

=IF(P2="","",DATEDIF(P2,TODAY(),"d"))

and for weeks

=IF(S2="","",INT(S2/7))

 

[EfficientOne]

It worked like a charm! Thank you very much.

 

[EfficientOne]

Now that the data is correct, I am given weeks 0 - 94 and of course, the blank. When I refreshed the pivot table it did update, but it will now not allow me to regroup by four. When I highlight the Weeks Since Ticket Last Changed in the pivot report and Group it only gives me 1 group whether I sort A to Z or vice versa.

 

[hazemrezo]

hi
i have question? ow arrange hours in excel but not like army settings but like 06:10 am and then come 7:25 am and then 1:05 pm i hope that i will find some one to help me thank you very much

 

[EfficientOne]

1. Click on the Column Header to highlight the entire column.
2. Right click and select 'Format Cells'.
3. Under Category select 'Time'.
4. Select the third option or whichever one best fits what you want your dates to look like.