[Solved] Calculation with date yields #VALUE!
-
- Posts: 28
- Joined: Tue Feb 17, 2009 5:01 am
[Solved] Calculation with date yields #VALUE!
Subtract one day from the date in C3 with result in C1 fails
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).
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).
OOo 4.0.1 on Ms Windows 7
Re: Why does this Date formula fail?
Your dates in the DATEACDT column are entered as text. There may be different reasons why this happens:
How to fix depends on your settings and workflow. The DATEVALUE() function may work for you.
Try
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.
- 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
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
Last edited by keme on Thu Mar 21, 2024 9:21 am, edited 1 time in total.
- MrProgrammer
- Moderator
- Posts: 4910
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Subtracting one day to a date 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.marty-0750 wrote: ↑Wed Mar 20, 2024 3:21 pm Subtract one day from the date in C3 with result in C1 fails.
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
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.marty-0750 wrote: ↑Wed Mar 20, 2024 3:21 pm Attempt to reformat from YYYY/MM/DD to YYYY-MM-DD failed too. Subornly stays at YYYY/MM/DD
That's because the text in column D is allowed as a time format for your locale.marty-0750 wrote: ↑Wed Mar 20, 2024 3:21 pm However time subtraction D2-9h30m = 3:29:09 in B2 works.
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.marty-0750 wrote: ↑Wed Mar 20, 2024 3:21 pm Column A: DATEUTC Column C: DATEACDT
Column B: TIMEUTC Column D: TIMEACDT
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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
-
- Posts: 28
- Joined: Tue Feb 17, 2009 5:01 am
Re: Subtracting one day from a date yeilds #VALUE!
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)
Martin
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)
Martin
OOo 4.0.1 on Ms Windows 7
-
- Posts: 28
- Joined: Tue Feb 17, 2009 5:01 am
Re: Subtracting one day from a date yeilds #VALUE!
Further clarification in attached.
OOo 4.0.1 on Ms Windows 7
Re: Subtracting one day from a date yields #VALUE!
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.
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.
Last edited by Alex1 on Fri Mar 22, 2024 11:22 am, edited 1 time in total.
AOO 4.1.15 & LO 24.2.2 on Windows 10
Re: Subtracting one day from a date yields #VALUE!
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;"")
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;"")
____________
DiGro
AOO 4.1.15 (Dutch) on Windows 11. Scanned with Ziggo Safe Online (F-Secure)
DiGro
AOO 4.1.15 (Dutch) on Windows 11. Scanned with Ziggo Safe Online (F-Secure)
- MrProgrammer
- Moderator
- Posts: 4910
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Subtracting one day from a date yields #VALUE!
I will assume you have read [Tutorial] Text to Columns. I imported the data above with:DATE-TIME text.txt wrote: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
• 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?
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. 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.
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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
-
- Posts: 28
- Joined: Tue Feb 17, 2009 5:01 am
Re: Subtracting one day from a date yields #VALUE!
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.
Is there a comprehensive guide on Calc formula? The Help link is very limited.
Thanks
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.
Is there a comprehensive guide on Calc formula? The Help link is very limited.
Thanks
OOo 4.0.1 on Ms Windows 7
Re: Subtracting one day from a date yields #VALUE!
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.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
- Hagar Delest
- Moderator
- Posts: 32670
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: [Solved] Subtracting one day from a date yields #VALUE!
Have you checked the links at the top of the Calc section of the forum?marty-0750 wrote: ↑Fri Mar 29, 2024 5:16 am Is there a comprehensive guide on Calc formula? The Help link is very limited.
LibreOffice 24.2 on Xubuntu 24.04 and 7.6.4.1 portable on Windows 10