Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates in Excel 2007
Using Excel 2007 data has been imported from Crystal Reports into an Excel
2007 spreadsheet. Column D is Proc Date; column J is Confirm Date. Column L is used to calculate the difference between the two dates, L2 = J2 - D2. Column L is formatted with a general number to show the number of days between the two dates. While the Proc Date is available the Confirm Date is not always available, so the cell is blank. In the calculation, L2, the numer that is returned is -40190, indicating that it is taking January 1, 1900, and subtracting January 12, 2010. Is there a setting or some other way to prevent a default date from being inserted when there is none so that ##### shows in the calculated cell? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates in Excel 2007
= IF(COUNT(D2,J2)=2,J2 - D2,"")
or = IF(OR(D2="",J2=""),"",J2 - D2) -- David Biddulph Basenji wrote: Using Excel 2007 data has been imported from Crystal Reports into an Excel 2007 spreadsheet. Column D is Proc Date; column J is Confirm Date. Column L is used to calculate the difference between the two dates, L2 = J2 - D2. Column L is formatted with a general number to show the number of days between the two dates. While the Proc Date is available the Confirm Date is not always available, so the cell is blank. In the calculation, L2, the numer that is returned is -40190, indicating that it is taking January 1, 1900, and subtracting January 12, 2010. Is there a setting or some other way to prevent a default date from being inserted when there is none so that ##### shows in the calculated cell? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates in Excel 2007
=IF(J1<"",J1-D1,"####")
"Basenji" wrote: Using Excel 2007 data has been imported from Crystal Reports into an Excel 2007 spreadsheet. Column D is Proc Date; column J is Confirm Date. Column L is used to calculate the difference between the two dates, L2 = J2 - D2. Column L is formatted with a general number to show the number of days between the two dates. While the Proc Date is available the Confirm Date is not always available, so the cell is blank. In the calculation, L2, the numer that is returned is -40190, indicating that it is taking January 1, 1900, and subtracting January 12, 2010. Is there a setting or some other way to prevent a default date from being inserted when there is none so that ##### shows in the calculated cell? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates in Excel 2007
Thanks for the formula. It address the negative number for the difference of
the two dates. However, is there a way to prevent Excel from "assuming" a date of January 1, 1900, when no date is entered? "Atif" wrote: =IF(J1<"",J1-D1,"####") "Basenji" wrote: Using Excel 2007 data has been imported from Crystal Reports into an Excel 2007 spreadsheet. Column D is Proc Date; column J is Confirm Date. Column L is used to calculate the difference between the two dates, L2 = J2 - D2. Column L is formatted with a general number to show the number of days between the two dates. While the Proc Date is available the Confirm Date is not always available, so the cell is blank. In the calculation, L2, the numer that is returned is -40190, indicating that it is taking January 1, 1900, and subtracting January 12, 2010. Is there a setting or some other way to prevent a default date from being inserted when there is none so that ##### shows in the calculated cell? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates in Excel 2007
Thank you for the formulas. They provide a solution for the negative number
for the difference of the two dates when the one date is missing. However, is there a way to prevent Excel from "assuming" a date of January 1, 1900, when no date is entered? "David Biddulph" wrote: = IF(COUNT(D2,J2)=2,J2 - D2,"") or = IF(OR(D2="",J2=""),"",J2 - D2) -- David Biddulph Basenji wrote: Using Excel 2007 data has been imported from Crystal Reports into an Excel 2007 spreadsheet. Column D is Proc Date; column J is Confirm Date. Column L is used to calculate the difference between the two dates, L2 = J2 - D2. Column L is formatted with a general number to show the number of days between the two dates. While the Proc Date is available the Confirm Date is not always available, so the cell is blank. In the calculation, L2, the numer that is returned is -40190, indicating that it is taking January 1, 1900, and subtracting January 12, 2010. Is there a setting or some other way to prevent a default date from being inserted when there is none so that ##### shows in the calculated cell? . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates in Excel 2007
Yes, that's what either of my 2 formulae will do for you. 1 January 1900 is
the Excel date for a value of zero. If you don't want it to use that value of zero, you need to tell Excel not to do the arithmetic with that cell in that situation. If you tell Excel to do the arithmetic, and if the value in the cell is zero, then it is treated as 1 January 1900. -- David Biddulph Basenji wrote: Thank you for the formulas. They provide a solution for the negative number for the difference of the two dates when the one date is missing. However, is there a way to prevent Excel from "assuming" a date of January 1, 1900, when no date is entered? "David Biddulph" wrote: = IF(COUNT(D2,J2)=2,J2 - D2,"") or = IF(OR(D2="",J2=""),"",J2 - D2) -- David Biddulph Basenji wrote: Using Excel 2007 data has been imported from Crystal Reports into an Excel 2007 spreadsheet. Column D is Proc Date; column J is Confirm Date. Column L is used to calculate the difference between the two dates, L2 = J2 - D2. Column L is formatted with a general number to show the number of days between the two dates. While the Proc Date is available the Confirm Date is not always available, so the cell is blank. In the calculation, L2, the numer that is returned is -40190, indicating that it is taking January 1, 1900, and subtracting January 12, 2010. Is there a setting or some other way to prevent a default date from being inserted when there is none so that ##### shows in the calculated cell? . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates in Excel 2007
"David Biddulph" wrote: Yes, that's what either of my 2 formulae will do for you. 1 January 1900 is the Excel date for a value of zero. If you don't want it to use that value of zero, you need to tell Excel not to do the arithmetic with that cell in that situation. If you tell Excel to do the arithmetic, and if the value in the cell is zero, then it is treated as 1 January 1900. -- David Biddulph Basenji wrote: Thank you for the formulas. They provide a solution for the negative number for the difference of the two dates when the one date is missing. However, is there a way to prevent Excel from "assuming" a date of January 1, 1900, when no date is entered? "David Biddulph" wrote: = IF(COUNT(D2,J2)=2,J2 - D2,"") or = IF(OR(D2="",J2=""),"",J2 - D2) -- David Biddulph Basenji wrote: Using Excel 2007 data has been imported from Crystal Reports into an Excel 2007 spreadsheet. Column D is Proc Date; column J is Confirm Date. Column L is used to calculate the difference between the two dates, L2 = J2 - D2. Column L is formatted with a general number to show the number of days between the two dates. While the Proc Date is available the Confirm Date is not always available, so the cell is blank. In the calculation, L2, the numer that is returned is -40190, indicating that it is taking January 1, 1900, and subtracting January 12, 2010. Is there a setting or some other way to prevent a default date from being inserted when there is none so that ##### shows in the calculated cell? . . |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dates in Excel 2007
Thank you for the explanation. It is helpful when dealing with dates in
calculations. "David Biddulph" wrote: Yes, that's what either of my 2 formulae will do for you. 1 January 1900 is the Excel date for a value of zero. If you don't want it to use that value of zero, you need to tell Excel not to do the arithmetic with that cell in that situation. If you tell Excel to do the arithmetic, and if the value in the cell is zero, then it is treated as 1 January 1900. -- David Biddulph Basenji wrote: Thank you for the formulas. They provide a solution for the negative number for the difference of the two dates when the one date is missing. However, is there a way to prevent Excel from "assuming" a date of January 1, 1900, when no date is entered? "David Biddulph" wrote: = IF(COUNT(D2,J2)=2,J2 - D2,"") or = IF(OR(D2="",J2=""),"",J2 - D2) -- David Biddulph Basenji wrote: Using Excel 2007 data has been imported from Crystal Reports into an Excel 2007 spreadsheet. Column D is Proc Date; column J is Confirm Date. Column L is used to calculate the difference between the two dates, L2 = J2 - D2. Column L is formatted with a general number to show the number of days between the two dates. While the Proc Date is available the Confirm Date is not always available, so the cell is blank. In the calculation, L2, the numer that is returned is -40190, indicating that it is taking January 1, 1900, and subtracting January 12, 2010. Is there a setting or some other way to prevent a default date from being inserted when there is none so that ##### shows in the calculated cell? . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 displayed dates | Excel Discussion (Misc queries) | |||
Using Dates in Excel 2007 | Excel Worksheet Functions | |||
Excel 2007 comparing dates | Excel Discussion (Misc queries) | |||
Problems with dates in Excel 2007 | Excel Discussion (Misc queries) | |||
Difference in dates in Excel 2007 | New Users to Excel |