Excel ??? ?????? ?? ??? ?? ????? ???? ????

???? ID: 214094 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
???? ?????? ???????????? ?????? ???????? KB ?? ???????? ????? ?? ??? ???? ????? ????
???? Microsoft Excel 2001 ?? ???? ?? Mac ??????? ?? ???, ?? ??? ?????303216
(http://support.microsoft.com/kb/303216/ )
.


Microsoft Office Excel 2007, ?? ??? ?? ????Office ?????? Excel 2007 ??? ??? ?? ???? ??? ?????
(http://office.microsoft.com/en-us/excel/FX100646951033.aspx?CTT=96"&"Origin=CL100570551033)
.
??? ?? ??????? ???? | ??? ?? ??????? ????

?? ????? ??

??????

?? ??? ?? ??? ???? ????? ???? ?? ?? ???? ????? ???? ?? ??? ?????????? ?????? ?? ??? ?????? Microsoft Excel ??? ???? ?????? ?? ??? ?? ?????? ???? ?? ??? ?????

Excel ?????? ?? ??? ???????? ???? ?? ?????????? ?? ??? ??? ?? ??? ??? ????? ??? ?? ??? ???? ?? ?????? ?? ??? Excel ???? ??????, subtract, ?? ?????? ?? ??? ?? ???? ???? ???? ?? ?????? ?? ????? ????, ?? ??? ?????? manipulated ?? ?????? ?? ????? ?? ??? ????

In this system, the serial number 1 represents 1/1/1900 12:00:00 a.m. Times are stored as decimal numbers between .0 and .99999, where .0 is 00:00:00 and .99999 is 23:59:59. The date integers and time decimal fractions can be combined to create numbers that have a decimal and an integer portion. For example, the number 32331.06 represents the date and time 7/7/1988 1:26:24 a.m.

To help you perform complex date and time calculations, Excel includes many built-in date and time functions.

The TODAY() Function and the NOW() Function

TheTODAYfunction returns the serial number of today's date based on your system clock and does not include the time. TheNOWfunction returns the serial number of today's date and includes the time.

How Dates Are Sorted Based on Serial Number

In Excel, dates are sorted based on the serial number of the date, instead of on the displayed number. Therefore, when you sort dates in Excel, you may not receive the results you expect.

?????? ?? ???, ??? ?? ?????? ??? ????????? ?? ?? ?????? ?? ????? mmmm ?????? ?????? (????? ?? ???? ????? ????????? ???? ???? ??), ?????? ??? ??? ????????????????? ????? ????? ???? ????, ?????? ?? ???? ?? ???? ?????????? ?????? ??????? ?????? ????? ????

?????????? ?? ?????? ???? ?????? ????? ????

??????? ???????? comparisons ?????? ?? ??? ??? ?? ????? ???? ????, ??????? ???????? ?????? ?? ???? ??? ?? ???? ???? ??????? ?? ????? (????????? ??? ?? ??????)?

?????? ?? ???, ?? ?? ????? ????????????? ??????, ?? ??? ??? ????? ??? ?? ??? ?????? ?? ????? ???? ?? ??? ??????
   =IF(NOW()=DATEVALUE("10/1/92"),TRUE,FALSE)
????? FALSE, ???? ?? ??? ?? ?? ?? ??????? ?????? 10/1/92; ??? ???? TRUE ???? ???? ?? ?????? 10/1/92 12: 00: 00 ??? ??? ?? ???? ????? ??? ?? ???????? ????? ?? ??? ???, ?? ?? ??? ?????? ??? ????? ?? ???? ??? ????, ?? ????? ?? ???? ?? ??????? ?? ?????? ?? ????? ???????? ????? ?? ????? ????:
   =IF(TODAY()=DATEVALUE("10/1/92"),TRUE,FALSE)

?????? ????? ?? ??? ????? ???? ?? ??? ???? ????

?? ?? ?????? ?? ???? ?????? ?? ??? ????? ?? ?????? ?? ??? ???? ?? ??? ???? ????

?? ?? sometime ?????? ??? ???? ?????? ?? ??? ????? ?? ?????? ?? ????? ?? ??? ????? ????? ?? ????? ????
   ="mm/dd/yy"-NOW()
????"mm/dd/yy"?????? ?????? ??? ??????? ?????? ?? ??? ?????? ?? ????? ???? ??? ??????

?????? ?????, ?????? ?? ?? ?????? ?? ??? ???? ?? ??? ???? ?? ??? ???? ????

?????, ?????? ?? ?? ????????, ???? ??????? ?? ??????? ?????? ??? ??? ???? ??? ?? ???? A1 ?? A2 respectively, ?? ??? ???? ?? ?????? ?? ???? ???? ?? ??? ????? ????? ?? ???? ????:
  1. ??? ?? ????????????? ??????

    ?? ??? ?? ??????? ?????? ??? ?? ??????? ????
    Excel ?????????????


  2. ??????? ???????????????? ?? ???, ????? ????????? ?????????????.

    ?? ??? ?? ??????? ?????? ??? ?? ??????? ????
    Microsoft Excel ?????????????


  3. ????????????? ??? ????? ???? ???? ????:
       A1:    03/25/94 
   A2:    05/01/98


    ?? ??? ?? ??????? ?????? ??? ?? ??????? ????
    ???? A1 ?? A2

  4. ???? D1 ??? ????? ????? ???? ????:
    =YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2)<MONTH(A1),AND(MONTH(A2)=MONTH(A1), 
DAY(A2)<DAY(A1))),1,0)&" years, "&MONTH(A2)-MONTH(A1)+IF(AND(MONTH(A2) 
<=MONTH(A1),DAY(A2)<DAY(A1)),11,IF(AND(MONTH(A2)<MONTH(A1),DAY(A2) 
>=DAY(A1)),12,IF(AND(MONTH(A2)>MONTH(A1),DAY(A2)<DAY(A1)),-1)))&" months, 
"&A2-DATE(YEAR(A2),MONTH(A2)-IF(DAY(A2)<DAY(A1),1,0),DAY(A1))&" days"


    ?? ??? ?? ??????? ?????? ??? ?? ??????? ????
    ???? D1


    ???:??? ?? ?? ????????? ????? ?? ??????? ?? ??? ?? ?????, ????????? ???? ?? ??? ?????? ????? ????? ??? ??? ???? ????? ???, ?? ????? ??? ???? ??????
??? ?? ????? ??? ?? ????? ???, ?? ??? D1 ????????? ???? ??:
   4 years, 1 months, 6 days

?? ??? ?? ??????? ?????? ??? ?? ??????? ????
???? D1
???????? ????? Breakdown ?????, ?????? ?? ???? ?? ???
?? ????? ???? ?? ?? ??????? ?????, ?????? ?? ???????? ?? ??? ??? ???? ?? ???-??? ????? ????

???:??? ?? ?? ????????? ????? ?? ?? ??????? ?? ???????, ????????? ???? ?? ??? ???? ????? ??? ?? ??????? ?? ??? ???? ??????
   Time segment                     Formula
   ------------------------------------------------------------------------
   The remaining number of          =A2-DATE(YEAR(A2),MONTH(A2)-
   days between two dates,          IF(DAY(A2)<DAY(A1),1,0),DAY(A1))&"days"
   ignoring years and months
   

   The remaining number of          =MONTH(A2)- MONTH(A1)+IF(AND(MONTH(A2)
   months between two dates,        <=MONTH(A1), DAY(A2)<DAY(A1)),11,
   ignoring years and days          IF(AND(MONTH(A2)<MONTH(A1),DAY(A2)>=
                                    DAY(A1)),12,IF(AND(MONTH(A2)>MONTH(A1),
                                    DAY(A2)<DAY(A1)),-1)))&" months"

   The number of whole years        =YEAR(A2)-YEAR(A1)-IF(OR(MONTH(A2)<
   between two dates                MONTH(A1),AND(MONTH(A2)=MONTH(A1),
                                    DAY(A2)<DAY(A1))),1,0)&" years"
???:????? ??????? ?? "???", ?? "?????", ??? ?? & "????" ???????? ??? ?? ???????? ?? ?????, ?????? ?? ???? ?? ??? ??? ??? ???? ?? ??? ?? ????

???? ?? ?? ?????? ?? ??? ?????? ?? ??? ?? ??? ???? ?? ??? ???? ????

?????? ?? ??? ?? ????????, ???? ??????? ?? ??????? ?????? ??? ???? ???? A1 ?? A2 ??? respectively, ?? ??? ?? ?????? ?? ?????? ?? ??? ????? ????? ?? ???? ????:
  1. ??? ?? ????????????? ??????

    ?? ??? ?? ??????? ?????? ??? ?? ??????? ????
    Microsoft Excel ?????????????


  2. ??????? ???????????????? ?? ???, ????? ????????? ?????????????.

    ?? ??? ?? ??????? ?????? ??? ?? ??????? ????
    Microsoft Excel ?????????????


  3. ????????????? ??? ????? ???? ???? ????:
       A1:    03/25/94 
   A2:    05/01/98

    ?? ??? ?? ??????? ?????? ??? ?? ??????? ????
    ???? A1 ?? A2


  4. ???? D1, ????? ????? ???? ????:
       =NETWORKDAYS(A1,A2)

    ?? ??? ?? ??????? ?????? ??? ?? ??????? ????
    ???? D1


    "1071" ????? D1 ???? ??? ?????? ?? ??? ????

    ?? ??? ?? ??????? ?????? ??? ?? ??????? ????
    ???? D1
???:??? ?? ?????# ????????? ?? ??? ??? ????? ??????-??????? ????????????? ??.. ?? ??? ???? ?? ??? ????? ???????????? ToolPak???????? ?? ????? ?? ??? ????? ????, ?? ???? ????????? ?? ?? ???? ?? ??? ??????? ???? ????? ??? ?? ??? ??? ??? ???? ???

????? ???? Incrementally ??????

??? ?????? ?????? ????, ?????? ?? ????? ?? ?????? ??? ??????, ?? ??? ????? ????? ?? ????? ???????????? ?? ?????? ??? ?? ???? ?????? ?? ?????? ??????, ??value1,value2, ??value3?? ?????? ????? ?? ????, ??? ?? ???, ?? respectively ?????? ?? ??? ????
   =DATE(YEAR(reference)+value1,MONTH(reference)+value2,DAY(reference)+value3)
?????? ?? ???, ?? ????? ?? ??? ?????? ??????, ?? ??? ????? ??:
   =DATE(YEAR(DATEVALUE("6/20/96")),MONTH(DATEVALUE("6/20/96"))+1,
    DAY(DATEVALUE("6/20/96")))

??? ????? ?? ??? ????? ???? ?? ??? ???? ????

?????? ??? ?? ???? ???? ????

?? ?? ?????? ?? ??? ??? ??? ?? ?????? ?? ?????? ?? ??? ????? ?? ?? ???? ?? ??????? subtract, ?????? ?? ??????? ?????? ?? ?? elapsed ????, ???? ?? ????? ?? ???????????? ???? ??? ???? ????? ?? ??? ?? ?????? ?? ???? ?? ???, ?????? ?????? ???? ??? h:mm ??? ?????? ?? ????? ?????

????? ?????? ???, ??? C2 ?? D2 ???? ??? ?????= B2-A2, ?? ???? C2 ??????? ?????? ??? ???????? ??, ???? (?? ?????? ???, 0.53125, 12 ???? ?? 45 ???? ?? ??????? ?????? ????) ?? ????? ?????? ????????? ???? ???
   A1: Start Time   B1: End Time    C1: Difference  D1: Difference
                                        (General)       (h:mm)
   A2: 6:30 AM      B2: 7:15 PM     C2: 0.53125     D2: 12:45

?? ??? ?? ??????? ?????? ??? ?? ??????? ????
???? D2


??????? ??? ?? ??????? ??? ?? ??? midnight ???, ??? ?? 24-???? ?? ??? ???? ?? ??? ???? ???? ?????? ?? ?? ?????? ???? ?? ???? ?? ???? ??? 1, ?? ?? 24-???? ?? ???? ?? ???????????? ???? ??? ?????? ?? ???, ?? ?? ???? ?? ??? ????, ?? midnight ?? ???? ??? spans ?? ??? ?? ???? ????
   A1: Start Time    B1: End Time    C1: Difference  D1: Difference
                                         (General)       (h:mm)
   A2: 7:45 PM       B2: 10:30 AM    C2: 0.614583333  D2: 14:45
?? ?????? ??? ????, ????? ????? C2 ?? D2 ?????? ??? ?????:
   =B2-A2+IF(A2>B2,1)

????? ?? ???? 24 ????? ?? ???? Accumulate ???? ????

24 ???? ?? ???? ??? ?? ??? ?? ????????? ???? ????? ???, ??? ?? 37:30:55 ?????????? ?????? ?? ????? ?? ???? ???? ??? ?? ???? ????? ?? ??? ????? ?????? ?? ????? ???? ????? ???, ?? ?? ????? ??? ???? ???????? ?????? ?? ??? ??????, ?????? ?? ???:
   [h]:mm

???? ????? ?????? ?? ???? ?? ?????? ??? ?????? ???? ?? ??? ???? ????

???????????? ?????? (h:mm:ss) ?? ????? ?????? ??? ?????? ???? ?? ??? (0.00), ???? ?????? ??????? ?????? ??? ?????? ???? ??? ????? ?? ?? 24-???? ?? ???? ??? ?????? ??? ?? ??? ??? ?? ??????????? 24 ?????? multiplying ??????
   =Time*24
???????:?? ?????? ?? ?? ?? ?? ????? ?????? ?? ???? ??? ?????? ??? ?????? ???? ????? ???; ?? ?????? ???? ???? ?????? ?? ??? ???????? ?? ?? ???????? ?? ???? ???TIMEVALUE?????? ?? ??? ???? ???..

?????? ?? ???, ??? ??? A1 ?? "4: 30" ??? ???? ?? 30 ???? ?? ???????????? ???? ?? ??? ?? ??? ??? ???, ?? ????? ??:
   =A1*24
?????? ??? 4.5 ???

??? ??? ?????? ?? ??? ???? ???? ???, ????? ????? ?? ????? ????:
   =(Time-INT(Time))*24
?????? ?? ???, ??? ??? A1 ???? "6/20/96 4: 30 AM", ????? ??:
   =(A1-INT(A1))*24
?????? ?? ??? ?? 4.5 ???

???? ?????? ?? ???? ?? ????? ?????? ??? ?????? ???? ?? ??? ???? ????

????? ?????? (0.00) ?? ?????? ???? ?????? ?????? ??? ?????? ???? ?? ??? (h:mm:ss), ???? ?????? ??????? ?????? ??? ?????? ???? ??? ????? ?? ?? 24-???? ?? ???? ??? ?????? ??? ?? ??? ??? ?? ??????????? 24 ?????? ?????? ?? ??????
   =Time/24
???????:?????? ?? ?? ?? ????? ?????? ?? ???? ?? ??? ??? ?????? ??????? ?????? ??? ?????? ???? ????? ?? ???? ?????? ?? ??? ???????? ?????? ?? ???? ??? ?????? ?? ???, ??? ???? ??? 4.5 ??? A1 ??? ??? ???? ?? 30 ???? ?? ???????????? ???? ?? ??? ??? ??, ?? ????? ??:
   =A1/24
?????? ?? 4: 30 ???

??? ?? ??? Microsoft Excel ??? ?? Windows ?? ??? Excel ?? ??? ??????? ?? ?????????? ???? ????

???????? ??? ??, Excel ?? ??? ?? ??? 1904 ?????? ??????? ?? ????? ???? ?? ?? Windows ?? ??? Excel 1900 ?????? ??????? ?? ????? ???? ??? ???? ???? ?? ?? ?? ?? ??? Excel ??????? ?????? 1 ??? ?? ??? ????? ?? ??? ???? ?????? ?? ??? ??? ???????? ??, Excel ????????? ???? ?? ??? ?? ??? ???1/2/1904 12: 00 ???Windows ?? ??? Excel ??????? ?????? 1 ?? ??? ??? ????????? ???? ??1/1/1900 12: 00 ???

??? ?? ??????? ??????????? Excel ?? ??? ?? ??? Windows ?? ??? Excel ?? ???, ?? ???? ?? ?????? ???????? ??? ????? ???? ?? ???? ??????, ?????? ?????? ??? ???????? ????? ???????? ?? ???????? ???????, ??? ?? ?? ????????? ????? ?? ?? ??????? ???????????? originated ????? ?????? ???????? ?? ??????? ?? ??? ???????, ?????? ????????? ?? ???? ?? ??? ???? ?? ?? ??? ???? ??? ?????? ?? ????

????? ?? ??? Excel ??? 1904 ?????? ??????? ?? ??? Windows ?? ???, ????? ????? ?? ???? ????:
  1. ????? ????????????? ??,??????.

    ?? ??? ?? ??????? ?????? ??? ?? ??????? ????
    Windows ?? ??? Microsoft Excel


  2. ????? ???????????, ?? ???? ??? ?? ??? ???? ?? ??? ????? ????1904 ?????? ?????????? ?????.

    ?? ??? ?? ??????? ?????? ??? ?? ??????? ????
    ?????? ????? ?????


????????? ???? ?? ??? Excel ??? 1904 ?????? ??????? ??? ?? ???, ????? ????? ?? ???? ????:
  1. ????? ????Excel???? ??,????????????.

    ?? ??? ?? ??????? ?????? ??? ?? ??????? ????
    ??? ?? ??? Microsoft Excel


  2. ????? ???????????, ?? ???? ??? ???? ???? ?? ??? ????? ????1904 ?????? ?????????? ?????.

    ?? ??? ?? ??????? ?????? ??? ?? ??????? ????
    ????? ????? ??? ????????????

????? ?? ???? ???? | ??????????? ???

??????

???? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ????? ???? ???????? ????? ????::
214233
(http://support.microsoft.com/kb/214233/ )
??? ?? ?????? ??? Excel unintended ?????? ?????? ??? ?????? ????
214386
(http://support.microsoft.com/kb/214386/ )
Microsoft Excel ??? ?????? ???
241072
(http://support.microsoft.com/kb/241072/ )
?????? ?? ??? ??? Excel ??????? ?? ????? ??? ?? ??????? ?? ??? ??? ????????? ????
264372
(http://support.microsoft.com/kb/264372/ )
???????? ?? ??? Excel ?????? ???? ????? ????? ??? ???????? ?? ????? ?? ??? ???? ????
????? ?? ???? ???? | ??????????? ???

???

???? ID: 214094 - ????? ???????: 20 ??????? 2011 - ??????: 3.0
???? ???? ???? ??:
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
??????: 
kbnomt kbgraphxlink kbscreenshot kbdatetime kbautomation kbprogramming kbfunctions kbhowtomaster kbmigrate kbmt KB214094 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:214094
(http://support.microsoft.com/kb/214094/en-us/ )
????? ?? ???? ???? | ??????????? ???

??????????? ???

 
????? ?? ???? ????????? ?? ???? ????