SOLVED

Excel date system

%3CLINGO-SUB%20id%3D%22lingo-sub-315762%22%20slang%3D%22en-US%22%3EExcel%20date%20system%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-315762%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20have%20recently%20%3CSTRONG%3Eremoved%20time%20%3C%2FSTRONG%3Efrom%20date%20by%20using%20the%20following%20function%20%3DDATE(YEAR(A7)%2CMONTH(A7)%2CDAY(A7))%20then%20all%20date%20have%20been%20changed%2C%20why%20%3F%20the%20date%20before%20apply%20function%20was%26nbsp%3B%3CSTRONG%3E2019-01-01%2017%3A32%3A45%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Ethe%20date%20after%20applying%20function%20have%20been%20converted%20to%20%3CSTRONG%3E1900-01-01%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20wanted%20the%20date%20in%20the%20cell%20be%20just%202019-01-01%3C%2FP%3E%3CP%3Ewhy%20this%20happen%26nbsp%3B%3F%20How%20can%20i%20solve%20this%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-315762%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-319302%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20date%20system%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-319302%22%20slang%3D%22en-US%22%3E%3CP%3EBy%20the%20way%2C%20you%20may%20simplify%20your%20formula%20using%3C%2FP%3E%0A%3CPRE%3E%3DINT(A7)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-318056%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20date%20system%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-318056%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20show%20%220%22.%20I%20solved%20it%20by%20adding%20new%20column%20and%20applying%20remove%20time%20function%20by%20choosing%20data%20from%20date%20cell%20%3A%20)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVery%20thanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-316188%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20date%20system%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-316188%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20try%20it%20I%20get%202019-01-01.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChange%20the%20number%20format%20of%20A7%20to%20%22General%22.%20What%20does%20show%20as%20a%20numeral%20value%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have recently removed time from date by using the following function =DATE(YEAR(A7),MONTH(A7),DAY(A7)) then all date have been changed, why ? the date before apply function was 2019-01-01 17:32:45

the date after applying function have been converted to 1900-01-01

I wanted the date in the cell be just 2019-01-01

why this happen ? How can i solve this ?

 

thanks

3 Replies
Highlighted

Hello

 

If I try it I get 2019-01-01.

 

Change the number format of A7 to "General". What does show as a numeral value?

 

Highlighted

Hello

 

It's show "0". I solved it by adding new column and applying remove time function by choosing data from date cell : )

 

Very thanks 

Highlighted
Solution

By the way, you may simplify your formula using

=INT(A7)