TOT: Strange sums in spreadsheet

Oct 05, 2013 9 Replies
Saved reading
Jump to replies (9)

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


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?

Look at this.

formatting link

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.

Perhaps his name is richard.

Fascinating! Thanks all.

Hugh

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.

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?

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.

Hugh Newbury scribbled...

RTFM

Here are some free ones

formatting link

Join the Discussion

Have something to add? Share your thoughts — no account required.

Didn't find your answer?

Ask the community — no account required