TOT: Strange sums in spreadsheet

I have a spreadsheet where most of the columns are in number format with

2 after the decimal point. Without thinking I put the dates in the first column in the format 05/10/13.

The numbers in that column came out as 5-figures-decimalpoint-00. Eg

22/06/13 came out as 41447.00.

For the life of me I can't work out how it reached that answer, given the input.

Any ideas anyone?

Hugh

Reply to
Hugh Newbury
Loading thread data ...

Seriously - for the life of you? I suspect if there was a actually a gun at your head you'd try to think harder.

How many years does 41447 days represent, roughly? Given that the current year is 2013 does that give you a clue as to what date day zero might be set to?

Reply to
Dave Baker

Look at this.

formatting link

Tim

Reply to
Tim+

Yes. 41447 is the number which Excel (and presumably other spreadsheets) recognises as 22nd June 2013.

Enter 41447 into a cell, then format the cell (Format - Cells - Number

- Date) with one of the date formats and see what happens.

1 is Jan 1st 1900. Today's date (5th Oct) is 41552.

If you want to input the dates manually, format the cells (Format - Cells - Number - General). The advantage of using a number and a date format is that you can quickly put in a large number of dates into a range by using a formula.

Reply to
John J Armstrong

Perhaps his name is richard.

Reply to
Tim Streater

Fascinating! Thanks all.

Hugh

Reply to
Hugh Newbury

Have a look at

formatting link

In the good old days of computing where dates were not inbuilt we had to write our own routines. In particular we had to add and subtract dates or do something like "every 4th Saturday". Julian dates were the easiest and safest way of doing it. Wiki gives some formula. I still have the original algorithm we used somewhere.

Day 0 can be any arbitrary date if you use your own routines. I don't think we needed to go back to earlier than 1900.

Of course, trying to squeeze things into as small a data space as possible we fell into the trap of 2 digit years. Convert to Julian, do calculations, convert back to date and store dd/mm/yy. No idea why we didn't always store Julian so we were affected by the year 2000 problem. To be fair we never expected stuff written in the early '80's to still be running 20 years later.

Reply to
AnthonyL

I find it quicker just to input the starting date and drag that down as far as required. Several years takes only seconds. Why complicate matters?

Reply to
Bob Eager

It also means that you can perform arithmetic on dates - for example to calculate an interval between two dates by subtracting one from the other. You can do the same with intervals of less than a day. For example, a value of 0.5 represents 12 hours, etc.

Reply to
Roger Mills

Hugh Newbury scribbled...

RTFM

Here are some free ones

formatting link

Reply to
Artic

HomeOwnersHub website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.