[Solved] Calculation with date yields #VALUE!

[marty-0750]

Subtract one day from the date in C3 with result in C1 fails

Date convert.jpg (31.82 KiB) Viewed 1357 times

Attempt to reformat from YYYY/MM/DD to YYYY-MM-DD failed too. Subornly stays at YYYY/MM/DD.
However time subtraction D2-9h30m = 3:29:09 in B2 works

How can I fix please?

Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).

[keme]

Your dates in the DATEACDT column are entered as text. There may be different reasons why this happens:

• The entries do not conform to the date interpretation pattern(s) defined for your locale
• The column is formatted as a text column
• Content is imported from a source where it is specified as textual data

Text content displays verbatim, regardless of number formatting applied to the cell (it is possible to add a component to the format code for handling text content, but that will not modify the text as such).
How to fix depends on your settings and workflow. The DATEVALUE() function may work for you.
Try

Code: Select all

=DATEVALUE(C2)-1
or 
=DATEVALUE(SUBSTITUTE(C2;"/";"-"))-1

You may also want to check the time value returned in the TIMEUTC (B) column. I suspect that there are text values in ACDT (D) column also, which may not be handled as you expected.

[MrProgrammer]

Subtract one day from the date in C3 with result in C1 fails.

I suspect your values in columns C and D are text but cannot check because you did not attach your spreadsheet. Calc's ability to perform arithmetic on text is limited. For a calculations with a text date, the date's form must one which is allowed for your locale, or it must be in YYYY-MM-DD form. No one can really help much until you reveal your locale. Your locale is set in OpenOffice.org, Preferences, Language Settings, Languages, Locale setting on a Mac and in Tools, Options, Language Settings, Languages, Locale setting on other platforms.

How did you create a spreadsheet full of text dates? Did you import a text file, say CSV? If so, the text date and time fields were caused by an incorrect import. Perform the import again, mark the date field as Date (YMD), and specify Detect Special Numbers as explained in this tutorial.
[Tutorial] Text to Columns

Attempt to reformat from YYYY/MM/DD to YYYY-MM-DD failed too. Subornly stays at YYYY/MM/DD

Read section 1. Types of data in Ten concepts that every Calc user should know. Numeric formats do not apply to text values. Your Convert text date to valid Calc date format topic was the same problem — text dates.

However time subtraction D2-9h30m = 3:29:09 in B2 works.

That's because the text in column D is allowed as a time format for your locale.

Column A: DATEUTC   Column C: DATEACDT
Column B: TIMEUTC    Column D: TIMEACDT

First you must re-import your data so the dates and times are numeric values, not text. Then if the intent is to convert from ACDT to UTC you will want to sum columns C and D to create a datetime, then adjust that by the correct offset for the ACDT time zone. For time zone conversions it will be more complicated to adjust the date and time separately. Dates and times are covered in sections 3 and 4 of the Ten Concepts tutorial.

If you need any additional assistance attach a spreadsheet demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). I will not help further unless you attach.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[marty-0750]

Thank you folks for the tips. Not sure how to apply "DATEVALUE=.." in the field

I include a few lines of the original generated .txt and .ods

I have made the last field ACST 6h00m where the UTC date should be 2022/01/11 (example in .ods file)

DATE-TIME text.txt

(176 Bytes) Downloaded 20 times

DATE-TIME calc.ods

(15.48 KiB) Downloaded 18 times

Martin

[marty-0750]

Further clarification in attached.

DATE-TIME calc(1).ods

(26.71 KiB) Downloaded 23 times

[Alex1]

When you pasted the textfile into the spreadsheet you should have checked Detect special numbers, otherwise the result is text.
The formulas in column A refer to themselves, which is wrong. The IF function must have an else part. In column B you compare the values in column D with 0.0395833, which corresponds with 57 minutes, whereas you use 0.3958 in column B, which corresponds to 570 minutes. In rows 2 to 5 you multiply the result with 24, which is wrong.

[DiGro]

Use Search and Replace to convert your text to numeric values.

Search for : .* (= dot+star)
Replace by: &

Make sure you've checked Regular expressions under "More"

You probably want to change the column A in the formula in A2 to C : so =IF(D2>0,0395833;C2-1;"")

[MrProgrammer]

Produced by SQM Reader Pro 2.2.0.0
Year/Month/Day,Hour/Minute/Second
2022/01/12,22:38:40
2022/01/12,22:39:41
2022/01/12,22:40:41
2022/01/12,22:41:41
2022/01/12,22:42:41

I will assume you have read [Tutorial] Text to Columns. I imported the data above with:
• Detect Special Numbers
• From row 2
• Separated by comma
• First field as Date (YMD)
• Second field as Standard
This fixed your problem with text dates. I explained all of this in my earlier post and tutorial. Did you not read it?

Further clarification in attached.

202403251018.gif (53.54 KiB) Viewed 844 times

Your UTC time calculations above are all bogus. It should be obvious that they are all incorrect because the seconds should not change when subtracting 9½ hours. You can check using this Time Zone Converter. Here is the first one.

202403251050.gif (40.65 KiB) Viewed 844 times

B2:B5 in DATE-TIME calc(1).ods are wrong because you multiplied by 24. The entire approach is wrong because you should not adjust date and time independently. I've attached a spreadsheet showing how to do the calculations. It calculates 2022-01-12 13:08:40 for the first row. It calculates 2022-01-11 20:30:00 for the last row. Adjust my DateTime style if you prefer to see the date as 2022/01/22.

202403251043.ods

(13.67 KiB) Downloaded 16 times

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[marty-0750]

Hi again

Finally got it to work. All the numbers look right now. It was that last condition in the formula that was missing. It needs to know what answer is required in column A regardless.

DATE-TIME calc(3).ods

(12.52 KiB) Downloaded 15 times

Is there a comprehensive guide on Calc formula? The Help link is very limited.

Thanks

[robleyd]

There is a little trap with UTC +9:30 - Alice Springs and the whole Northern Territory, together with all of South Australia share the same time zone; however only South Australia observes daylight saving time.

[Hagar Delest]

Is there a comprehensive guide on Calc formula? The Help link is very limited.

Have you checked the links at the top of the Calc section of the forum?