[Solved] #VALUE! result when adding 7 days to a date

Discuss the spreadsheet application

[Solved] #VALUE! result when adding 7 days to a date

Postby tricia.young » Fri Jan 17, 2020 5:24 pm

Help! I imported a spreadsheet from MS Office over a year ago and everything was working fine. it went out years, each date was accurate. Now I've deleted a bunch of previous year's columns and the date formatting no longer works; I enter a date in the first column and place +(previous column)+7 for seven days later (1 week) and it is returning #Value instead of the date. All columns are formatted Date. So I downloaded the latest vs. of office and it remains the same. What am I doing wrong?
Last edited by RoryOF on Tue Jan 28, 2020 3:42 pm, edited 3 times in total.
Reason: Added green tick [RoryOF, Moderator]
OpenOffice 4.1.7 on Windows 7
tricia.young
 
Posts: 2
Joined: Fri Jan 17, 2020 5:18 pm

Re: #VALUE! result when adding 7 days to a date

Postby Villeroy » Fri Jan 17, 2020 11:54 pm

If A1 contains a text value =A1+7 returns #VALUE. Apply any number format but "Text" and re-enter the value. If there is a whole column of text dates, select the column and call Tools>Text to Columns... in order to re-enter all values.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28537
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: #VALUE! result when adding 7 days to a date

Postby jrkrideau » Sun Jan 19, 2020 11:57 pm

tricia.young wrote: All columns are formatted Date. So I downloaded the latest vs. of office and it remains the same. What am I doing wrong?


Most likely the cells are formatted as date but for some earlier formatting reason they are actually text. Are the entries right adjusted (number/date) or left adjusted (text)?
LibreOffice 6.4.3.2; Ubuntu 20.04
jrkrideau
Volunteer
 
Posts: 3788
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: SOLVED: #VALUE! result when adding 7 days to a date

Postby tricia.young » Tue Jan 28, 2020 3:22 pm

Thanks for the tip guys! I found when I typed the date into the first couple columns, the date formatting worked thereafter, so I'm good now.
OpenOffice 4.1.7 on Windows 7
tricia.young
 
Posts: 2
Joined: Fri Jan 17, 2020 5:18 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 10 guests