iOS Numbers returning DATEVALUE result as a date

Obviously there is a discrepancy between how Microsoft considers DATEVALUE functions and how Apple thinks it functions. According the the Excel doc, DATEVALUE "Returns the serial number of the date represented by date_text. Use DATEVALUE to convert a date represented by text to a serial number." The Apple doc you pointed to indicates that it returns the value as another date in a slightly different format. Basically the Apple version is non-functional. I need the date converted to a number in order to do subsequent calculations.

JohnD

Thanks for your reply. I found a NUMBERS function (DATEDIF) that will do what I want to do. I am trying to convert a date to its serial number equivalent so I can do further calculations with it. By using DATEDIF and the date 12/30/1899 as the starting date, I can reproduce what the EXCEL DATEVALUE function does.The only problem with DATEDIF is that it is not a valid EXCEL function. so there is no way to seamlessly transition from EXCEL to NUMBERS without an error. I am trying to set it up so others might be able to use this without having to go in and manually redo a cell especially since they may not be that familiar with iOS NUMBERS. I was thinking of creating a user function in EXCEL to reproduce the NUMBERS DATEDIF function, but I don't know how NUMBERS would interpret that.

JohnD

Well lo and behold. EXCEL comes through. I set up a formula with an IF statement:

IF (O5="EX", DATEVALUE(C11),DATEDIF(M5,C11,"D"))

Cell C11 contains the date to convert.

Cell O5 contains an indicator as to whether I was using EXCEL or NUMBERS.

Cell M5 contains the initial starting date (12/30/1899) to calculate the date difference.

As long as I don't try to execute the second half of the IF statement, EXCEL is perfectly happy with the invalid function in that second half of the IF. When I import the spreadsheet to NUMBERS on the iPad, I get an error because it does not like using the result of the DATEVALUE function in a calculation. All I need to do is remove the indicator in cell O5, and NUMBERS is happy and correctly computes the the difference value.

JohnD

Well one step forward and two steps back. NUMBERS is choking on all my INDIRECT references such as INDIRECT("$A$"&ROW()-1). I tried just to manually enter a cell to test what NUMBERS is looking for and it keeps giving me an error that it finds a list where it expects a single entry. How does one enter such a reference?

JohnD

I am in a habit of using the absolute cell reference "$". I have run into some situations where using the relative reference caused me problems. I could remove those without a problem. In fact I am trying that now.

I was originally attempting to create an EXCEL spreadsheet on my PC to calculate the position of alignment stars for a telescope. Once created the idea came to me to be able to load this on my iPad so I could use it in the field. The spreadsheet works well on the PC, the only problem I have is porting it to the iPad. There are apparent differences between EXCEL and NUMBERS. I have been able to find workarounds for just about everything. The INDIRECT references, I believe, are the last issue. I am going through the spreadsheet again and trying to remove the INDIRECT references to see if the spreadsheet still functions properly. If it does, then I will go with that.

The error I am getting when I try to enter a formula with an INDIRECT reference is "The formula contains a list where a single argument is expected". The NUMBERS User Interface for entering formulas takes some getting use to. It is not the free-form format that I am use to with EXCEL. I tried entering a test formula but I continue to get that error. I have attached a screen print.

Thanks,

JohnD

Thanks,

I came to that same conclusion and that works. I am not sure what NUMBERS does not like about that type of coding from EXCEL. I will play with it some more. I think I might need it in some instances.

JohnD

I am still having an issue with INDIRECT statements. NUMBERS is rejecting them indicating that the function is unsupported. Some of these statements are essential and I don't know if there is another way around it. I believe I will follow your suggestion about checking into the Mac forum and seeing if someone has any suggestions there as to any workarounds.

Thanks for your help,

JohnD