• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

What Is Date in Excel? it is real calender or just numbers counted from 01-01-1900?

Dear All,

What is date in computer system or in excel? calendar Or Number?
it is real calendar? with all leap year effects? like 28 or 29 days in feb?
with all 30 days or 31 days month effect?
OR
it is just number that series of days or hours or minutes or seconds pass after 01-01-1900 without effects of calendar?

we can relay on dates of computer or excel in terms of calendar?

Is date type variable hold whole calendar from 01-01-1900 with all leap years (28 or 29 days of feb.), with 30 or 31 days of all months effect?

what precautions we can take when we write date in excel as in terms of calendar?

Please help to understand this thing.

Regards,

Chirag Raval
 
Dear Si @Marc L ,

Thanks for reply,

I try it , its formatted as date & display 11/11/2001.

Means , pure seconds, minutes, hours, days, months, years passed,
after 01-01-1900 ? without effect of leap years, & without effects of some months have 30 days some 31?

That means this number ,formatted as date, have not any relation to any real calendar?

That means excel date is not calendar date.?

Please Guide.

Regards,

Chirag Raval
 
... DateTime in any sort of programming/database is represented by some sort of numeric representation.

UNIX Timestamp (Epoch) = # of seconds since Jan 1, 1970

Excel DateTime = Whole # represents # of days since Jan 1, 1900. And time portion is represented by decimal value 1day = 24/24, 1 hour = 1/24.

All systems accounts for leap year, and # of days in months etc, when it's converted into date format.
 
From excel online help:
Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2010 is serial number 40179 because it is 40,178 days after January 1, 1900.

The largest possible date value is 2958465, or 31/12/9999.

So in between those 2 extreme values (dates), Excel knows exactly the calendar date the number (from 1 until 2958465 included) represents.
 
Dear Sirs @Chihiro & +GraH - Guido,

Thanks for your efforts,
Sir Chihor's statement
"All systems accounts for leap year, and # of days in months etc, when it's converted into date format."

Means after convert number in to date,
It cover with all effect of leap year and 30 or 31 days effects?
But how can we check it?

All statement seems try to differ
Pc date & Real calender.

Why till not now ( may be ignore) or explicitly explain this
Matter anywhere? & Just explain only
Strait forward Number from 01-01-1900?

Though ,in universe, time is constantly flow, & atomic clock Precisely count time
In Nemo second level, unfortunatly / may be earth's unstable round movement is not as precise as
Real Time which efact on calender, hence humans try to match this unstable rounding as days & night sequence
As calender form . Like leap year & 30 - 31 days of month.

That we can differ preciseness of time as Nemo seconds & unfixed day and night.

So why humans try to join both diffrent aspects in calender?

There are time to need explicitly declare that real time is diffrent thing which used by machine then calender of this earth.

humans should relay on real time & not on calendar. Because calender is just unsuccessful try to fusion of real time with earth's round movement.

Every machine/ pc have real time, not real calender.

I can't explore or think more about humans calender & real time.

May be The greattest & rare miracle of this world, respected Mr Albert Einstein, if living ,yet, can be explore how precisly combine real time & calender.

Till now , i think machine's counting of time is only real & not calender. Both are different aspects.

Am I right or wrong?

Please declare reality in terns of machine & calender.

Regards,

Chirag Raval
 
Last edited:
Hi Chirag,
the existence and need of a calendar is a long and heavily discussed and updated topic, which I'm sure you can look up if you wish to know more.
Suffice to say we have a calendar, and leap years are a correction introduced to keep years in time with seasons.

This aside, if you start at 01/01/1900 and count up in days, you get the excel date number.
I don't really understand your issue with this;
01/01/1900 + 10 days = 11/01/1900 (i'm English)
1 + 10 = 11
23/04/2018 + 10 days = 03/05/2018
43213 + 10 = 43223

They don't behave differently, they are just different formats.

*Aside from the 1900 leap year error as Debaser mentioned
 
Dear all experts,

I really do not any problem with dates recognised by machine.
Every one accept that machine do right because there are naturally
01-01-1900+ 1 = 02-01-1900 & it only can precisely maintain like atomic clock.

But there are need to perelal construct & maintain in systems real calender that
Adjust & Covered earth's round movement around the great star Sun. Because,
It is an aspect that effect on human & nature.

May be astronomy & geological aspect which vastly effect on life of
This earth, already maintain this that's not part of Excel yet,

But iIt should be also be part as basic level of every computer system
That every users of systems can To be aware about relation of earth's movement
Relative to day & night, time zone, seasons etc that's real effect on life.

Final conclusion is machine date is not based on nature calender
& Cover earth calender with some limitations.
But machine's date- time is real date time as per methemetical &
Scientific aspect.

Boths are true , Boths stands forever
& Mainly Boths are not opposite to each other But main interesting pont is boths depends on each other & help to maintain
Many things in everyday life.

Just Great & many thanks to every one for contribute their aspect upon this matter
& hope May be some one have more to add
For further understand things.

Regards,

Chirag Raval
 
Last edited:
Any system whether manual or a computer system models dates and times so that we have a system that is universal and repeatable.
The key word though is "Models"

We need to model the dates and times as human like consistency

So yes, all computer systems have a nomenclature where Integers are Dates and Decimals are times.
These work remarkably well in all circumstances after 1 Jan 1900 and with minor tweaks for dates before 1900.

Unless you are doing advanced astrological measurements or planning the aberrations in the earths orbit and spin which also add variability to the Dates/Times don't really come into play in any meaningfull way in the day to day of our lives

Remember that computers only understand binary and so at the lowest level, all data including dates/times must be converted to strings of 1's & 0's, so finally answering the original question, No computers don't understand Dates or Times
 
Dear Sir, @Hui,

Many thanks for help in reach near conclusions about what is date & time.

so conclusion is.

"Remember that computers only understand binary and so at the lowest level, all data including dates/times must be converted to strings of 1's & 0's, so finally answering the original question, No computers don't understand Dates or Times"

May be about this aspect , every machine users can little conflicts / guess questions/ think , that , as you declare, should be explicitly declare by scientist when, machines start to become part of life.

Anyway , great points to learn now & here to understand date / time & calendar.

Today , sky science , need this aspect, May be , in future, artificial intelligence will need & cover this point , about earth's movement & it can be little effects on its that time's decision , just my guess..not sure, )

Thank you very much sirs.

Regards,
Chirag Raval
 
Last edited:
Imagine if everyone provided his/her address in UTM coordinates then it will provide accurate location on this planet but will you be able to make out anything by merely reading it?

Importantly, we can measure something from a reference point. Time has existed way before humans came into being and will still be there even if we become extinct. Calendars have come much later when we humans started to think about these dimensions. So you can't really determine the 0 reference point. Even if we could it would be meaningless in our day to day lives. Even in language you will find phrases like "ab initio" and "ad infinitum".

So it is important to realize that certain things could be understood better by human mind when they are put in reasonably understandable reference context.

Following is the definition of second.
One second is the time that elapses during 9,192,631,770 (9.192631770 x 10 9 ) cycles of the radiation produced by the transition between two levels of the cesium 133 atom.

If one was to use this scientific definition of time elapsed like if someone said...3600 X 9.192631770 x 10 9 cycles between two levels cesium atom passed to describe an hour has passed then you'd feel hour was too longer :)
 
That means this number ,formatted as date, have not any relation to any real calendar?

That means excel date is not calendar date.?
What is the difference between an Excel date and a calendar date ?
None as both are dates ‼ Both Excel & any calendar respect dates rules …

Means , pure seconds, minutes, hours, days, months, years passed,
after 01-01-1900 ? without effect of leap years, & without effects of some months have 30 days some 31?
Yes as you must have yourself already check directly within Excel :
enter in D1 cell the first day of January 2018
then in D2 cell enter this very complex formula =D1+1
then copy down this formula until D31 cell.
Now change D1 cell for the first day of February
and just observe the four last cells !

So respecting dates rules, Excel is a « real calendar »
as a calendar is just a series of dates.
Word « real » is just superfluous …

The real question is « what is a date in Excel ? »
And you already have the answer since post #2
(& #4 for date & time so a time is a date in Excel) …
 
Dear all Sirs & experts, @Marc L & @shrivLlabha,

Thank you very much for kind efforts.

So as per Post no 2 & 4 + just above this post
Can we say that date type variable by default have whole calender
With effect of leap year & 30 -42 days variation.

Regards,

Chirag Raval
 
Dear Sir @shrivallabha & Sir @Marc L 7 Sir @Hui , Sir @Debaser , Sir @GraH - Guido , @Stevie

Thanks, all your effort is valuable & helpful forever.

I check Sir MarcL's simple formula as per your guide line & you are right, it displayed 29 days on feb-2016.

Thank you very much for declare as date variable Is by default have whole calendar to understand things that on that date variable base we can do anything precisely with related to date & time.

This is be very helpful to users to trust for date & time matters relation to calendar.

Thanks again to you all for contribute your valuable time & effort to stand the concept.

Regards,

Chirag Raval
 
Back
Top