Fiona has attended:
Excel Intermediate course
Excel Advanced course
Excel Dates
Hi,
I was shown how to add dates on Excel, by which i mean if i have a date in one box eg. 15th May 2009 i can enter a formula to add dates onto the date eg. =K3+7, except its not working for me, it is coming up with an #VALUE! error.
Can you please advise?
Thanks
Fi
RE: Excel Dates
Hello Fiona
Thank you for your question and welcome to the forum.
If you select the cell showing the #VALUE! error, a little tag with an ! on it should appear. If you click on this, does it give you any further information about the error?
Kind regards
Amanda
RE: Excel Dates
Thank you for your fast response.
i did this and it says
"Symptom
Excel displays the #VALUE! error in one or more cells in a worksheet.
Causes
One or more cells that are included in a formula contain text, and your formula performs math on those cells by using the standard arithmetic operators (+, -, *, and /).
For example, the formula =A1+B1, where A1 contains the string "Hello" and B1 contains the number 3, returns the #VALUE! error.
A formula that uses a math function, such as SUM, PRODUCT, or QUOTIENT, contains an argument that is a text string instead of a number.
For example, the formula PRODUCT(3,"Hello") returns the #VALUE! error because the PRODUCT function requires numbers as arguments.
Your workbook uses a data connection, and that connection is unavailable.
Example
When you copy the example data to a blank worksheet, the formulas in cells A4 and A5 return a #VALUE! error; however, cells A6 and A7 return the correct value of 30."
which goes against what i did in my training course and what i was told.
Thanks
fi
RE: Excel Dates
Hi Fiona
Would it be possible to send the spreadsheet through to me so I can have a look? Email is amanda@stl-training.co.uk
Amanda
RE: Excel Dates
Hi Amanda,
Thanks, i have just sent it over to you
Fi
RE: Excel Dates
Thanks Fiona
It is to do with how the dates have been entered into the spreadsheet.
If the dates are typed in as, for example, 15 May 2009, Excel recognises this as text (you will see that if you type the date into the cell like this and press Enter, the date left aligns meaning Excel recognises it as text).
Excel cannot use something that is text in a formula.
Instead what you will need to do is enter the dates as follows:
15/05/09
19/05/09
then the formulas will work.
If you would like the dates to appear as 15 May 2009, then change the formatting for the cells with the dates in them (select the cells, right-click, Format Cells, Number tab, select Date then the date format you'd like to use).
I hope this helps to explain the error you're seeing.
Kind regards
Amanda
