Excel 2007 vs 2010
-
Would there be any differences in how formulas are processed between 2007 and 2010?
I've got a spreadsheet which I'm guessing was created in 2010 and being used in 2007. It's saved in .xlsx format, but the formula isn't calculating correctly in 2007. And for <me> in either 2007 or 2010....
-
@g.jacobse said:
Would there be any differences in how formulas are processed between 2007 and 2010?
I've got a spreadsheet which I'm guessing was created in 2010 and being used in 2007. It's saved in .xlsx format, but the formula isn't calculating correctly in 2007. And for <me> in either 2007 or 2010....
What type of formula is it? It shouldn't work any different, unless it's a function found in 2010 but not in 2007, which would be odd, but possible. Can you provide more info?
-
Agreed, only a new function in 2010 MIGHT not work in 2007...
Does it work anywhere?
-
I'm not sure how much I can offer - it's a protected sheet, we are only allowed to enter some data. But there are 4 date fields and a day calculated from that.
Start Date; Release Date; First date of month, last date of month.
Here is the formula which is in column "K":
=IF(ISBLANK(H5),"",IF(ISBLANK(G5),"Enter START DATE to calculate",IF(AND(MONTH(EOMONTH($A$2,1))>MONTH(G5),MONTH(G5)>=MONTH($A$2)),IF(ISBLANK(F5,EOMONTH($A$2,0)-G5+1,H5-G5),"START DATE MUST BE >= "&TEXT($A$2,"mm/dd/yyyy"))))
Column Fields:
e= Arrival Date
f= Release Date
g=Start date
h= end date
k=#of days formula aboveBased on what I've tried,.. the formula works as long as you don't enter anything in the Release date. Once you enter the release date, the #of days drops by 1.
I'm told it works 'correctly' on one computer. I've tried two computers here one with 2010 and one with 2007 and get the same result. count is off by 1 (30 days rather than 31).
-
@g.jacobse said:
I'm not sure how much I can offer - it's a protected sheet, we are only allowed to enter some data. But there are 4 date fields and a day calculated from that.
Start Date; Release Date; First date of month, last date of month.
Here is the formula which is in column "K":
=IF(ISBLANK(H5),"",IF(ISBLANK(G5),"Enter START DATE to calculate",IF(AND(MONTH(EOMONTH($A$2,1))>MONTH(G5),MONTH(G5)>=MONTH($A$2)),IF(ISBLANK(F5,EOMONTH($A$2,0)-G5+1,H5-G5),"START DATE MUST BE >= "&TEXT($A$2,"mm/dd/yyyy"))))
Column Fields:
e= Arrival Date
f= Release Date
g=Start date
h= end date
k=#of days formula aboveBased on what I've tried,.. the formula works as long as you don't enter anything in the Release date. Once you enter the release date, the #of days drops by 1.
I'm told it works 'correctly' on one computer. I've tried two computers here one with 2010 and one with 2007 and get the same result. count is off by 1 (30 days rather than 31).
I believe the MONTH variable is 30 days. That's probably where the issue is.
-
Something I might should have done (it's more advanced Formulas than I know) - is copy the formula into a new clean workbook.
I do get an error: You've entered to many arguments for this function.
I also see that the formula in row 5 calls Row2 Col 1 which is current begin date of the month.
-
@ajstringham said:
@g.jacobse said:
I'm not sure how much I can offer - it's a protected sheet, we are only allowed to enter some data. But there are 4 date fields and a day calculated from that.
Start Date; Release Date; First date of month, last date of month.
Here is the formula which is in column "K":
=IF(ISBLANK(H5),"",IF(ISBLANK(G5),"Enter START DATE to calculate",IF(AND(MONTH(EOMONTH($A$2,1))>MONTH(G5),MONTH(G5)>=MONTH($A$2)),IF(ISBLANK(F5,EOMONTH($A$2,0)-G5+1,H5-G5),"START DATE MUST BE >= "&TEXT($A$2,"mm/dd/yyyy"))))
Column Fields:
e= Arrival Date
f= Release Date
g=Start date
h= end date
k=#of days formula aboveBased on what I've tried,.. the formula works as long as you don't enter anything in the Release date. Once you enter the release date, the #of days drops by 1.
I'm told it works 'correctly' on one computer. I've tried two computers here one with 2010 and one with 2007 and get the same result. count is off by 1 (30 days rather than 31).
I believe the MONTH variable is 30 days. That's probably where the issue is.
I can see that being the case. However: Why would it work on one computer and not on any other.
-
@g.jacobse said:
@ajstringham said:
@g.jacobse said:
I'm not sure how much I can offer - it's a protected sheet, we are only allowed to enter some data. But there are 4 date fields and a day calculated from that.
Start Date; Release Date; First date of month, last date of month.
Here is the formula which is in column "K":
=IF(ISBLANK(H5),"",IF(ISBLANK(G5),"Enter START DATE to calculate",IF(AND(MONTH(EOMONTH($A$2,1))>MONTH(G5),MONTH(G5)>=MONTH($A$2)),IF(ISBLANK(F5,EOMONTH($A$2,0)-G5+1,H5-G5),"START DATE MUST BE >= "&TEXT($A$2,"mm/dd/yyyy"))))
Column Fields:
e= Arrival Date
f= Release Date
g=Start date
h= end date
k=#of days formula aboveBased on what I've tried,.. the formula works as long as you don't enter anything in the Release date. Once you enter the release date, the #of days drops by 1.
I'm told it works 'correctly' on one computer. I've tried two computers here one with 2010 and one with 2007 and get the same result. count is off by 1 (30 days rather than 31).
I believe the MONTH variable is 30 days. That's probably where the issue is.
I can see that being the case. However: Why would it work on one computer and not on any other.
That is true. A curious case...check for a macro of some sort maybe?
