Addition Formula is Giving a Date!!!

[MikeM3]

Good afternoon.

I am new here looking for some guidance please.

While working on a cash flow page of Excel, I am gettig a strange
result.
Attempting to add a few cells together I entered the formula =SUM(B4:F4),
press enter and I get a date 18/03/1900, to try and fix it, I deleted all
and in 3 different columns I entered 100, 50, 50 then in the "G" I put the
above formula and got 19/07/1900.

I started with an old sheet and saved as, then deleted evrything from


it, but also lost the formula, so I pulled the original to copy them across,
the first one I wrote was =SUM(A4:E4), a being the column where the
date had been deleted from and got a date appear. If I put the formula
anywhere else on the sheet, it works properly.

I know very little about Excel, but I was shown many years ago how to
write formulas and how to move around a page, but that is all that I know.

Can somebody tell me what is wrong or what I am doing/not doing that
is causing this.

Mike.

[Kevin@Radstock]

Hi Mike

The cell which has the formula in, is it General formatting and not as a date!

[MikeM3]

Originally Posted by Kevin@Radstock

Hi Mike

The cell which has the formula in, is it General formatting and not as a date!

Hi Kevin, thank you for reading this.

I think it is general formatting.

Columns "A" to "G" are on the income side, with "A" being
the Date and G being the total, "B" to "F" are the income
streams.

Te formula =SUM(B4:F4) is in the "G" column, but it is not
doing the job.

There must be some hidden text or instructions in that cell
I think.

If all fails, I will just have to start with a fresh spreadsheet,
I tried to help myself by using an old one, but deleted all the
formula in the cells, so a fresh sheet will be clean.

Mike.

[Debaser]

Select the cell with the formula in. Press Ctrl+1. On the number format tab is it set to Date?

[MikeM3]

Thank you all for your suggestions and advice.

Being an engineer, I have dumped the offending sheet/page
and started with a fresh one that works as it should.

I am still confused with the faulty sheet and cannot think
how such an error can happen, I could understand a cash
sum being wrong, but not a date over 100 years popping up.

Thank you once again.

Mike.

[Debaser]

Originally Posted by MikeM3

Attempting to add a few cells together I entered the formula =SUM(B4:F4),
press enter and I get a date 18/03/1900, to try and fix it, I deleted all
and in 3 different columns I entered 100, 50, 50 then in the "G" I put the
above formula and got 19/07/1900.

Excel stores dates as the number of days since 31/12/1899.

In your example, 100 +50+50 = 200. 200 days from 31/12/1899 is 18 July 1900. Therefore the simple answer is that your formula cell was formatted as a date. All you needed to do was change the number format as mentioned and you would have seen the correct result.

[MikeM3]

Originally Posted by Debaser

Excel stores dates as the number of days since 31/12/1899.

In your example, 100 +50+50 = 200. 200 days from 31/12/1899 is 18 July 1900. Therefore the simple answer is that your formula cell was formatted as a date. All you needed to do was change the number format as mentioned and you would have seen the correct result.

Thank Debaser, I didn't know that, also I don't know how
I managed to put a date formula into a previously empty
cell, or why the formula that I put in didn't overwrite it.

Thank anyway.

Mike.

[Debaser]

It's not a date formula. The cell is formatted to display dates. All you needed to do was to select the cell with the SUM formula in it, press Ctrl+1 (or right-click it and choose Format Cells) and change the format on the Number tab from Date to something else.

[MikeM3]

Thank you once again Debaser, I will try to remember that
for another time.
I am not used to using excel and get lost occassionally.

Mike.

[Debaser]

You're welcome.

Excel can be hard to fathom - especially if you don't use it often!