1900 Excel¿¡¼­ 1 1 Àü¿¡ ³ªÀ̸¦ °è»êÇÏ´Â ¹æ¹ý

Microsoft Excel¿¡¼­ ³¯Â¥ ¼ö½ÄÀ» ³¯Â¥ »çÀÌ¿¡ ÀÔ·ÂÇÑ °æ¿ì¿¡¸¸ »ç¿ëÇÒ ¼ö ÀÖÁö¸¸ 1 1900³â ¹× 12/31/9999, ÀÀ¿ë ÇÁ·Î±×·¥ ÇÔ¼ö¿¡ ´ëÇÑ »ç¿ëÀÚ ÁöÁ¤ Microsoft Visual Basic ´©±º°¡°¡ ¶Ç´Â ¹º°¡ ³â) ¿¡¼­ ¸ÕÀú 1900³â 1¿ù 1ÀÏ Àü¿¡ ¸¸µç ³ªÀ̸¦ °è»êÇÒ ¼ö ÀÖ½À´Ï´Ù.

¸ÅÅ©·Î¸¦ »ç¿ëÇÏ¿© ³ªÀÌ °è»ê

Microsoft´Â ¹¬½ÃÀûÀÎ º¸Áõ ¾øÀÌ ¸ñÀûÀ¸·Î¸¸ ÇÁ·Î±×·¡¹Ö ¿¹Á¦¸¦ Á¦°øÇÕ´Ï´Ù. ÀÌ Æ÷ÇÔµÇÁö¸¸, »óÇ°¼º ¶Ç´Â ƯÁ¤ ¸ñÀû¿¡ÀÇ ÀûÇÕ¼º¿¡ ´ëÇÑ ¹¬½ÃÀûÀÎµÈ º¸ÁõÀÌ Á¦ÇѵÇÁö ¾Ê½À´Ï´Ù. ÀÌ ¹®¼­¿¡¼­´Â ¸¸µé·Á¸é ¹× ÇÁ·Î½ÃÀú¸¦ µð¹ö±ëÇÏ´Â µ¥ »ç¿ëµÇ´Â µµ±¸ ¹× ¿©±â¼­ ¼³¸íÇÏ´Â ÇÁ·Î±×·¡¹Ö ¾ð¾î¿¡ Àͼ÷ÇÑ »ç¿ëÀÚ¸¦ ´ë»óÀ¸·Î ÇÕ´Ï´Ù. Microsoft Áö¿ø ¿£Áö´Ï¾î´Â »ç¿ëÀÚ¿¡°Ô µµ¿òÀÌ µÇµµ·Ï ƯÁ¤ ÀýÂ÷¿¡ ´ëÇÑ ±â´ÉÀ» ¼³¸íÇÒ ¼ö ÀÖÁö¸¸ Ãß°¡ ±â´ÉÀ» Á¦°øÇϰųª ƯÁ¤ ¿ä±¸ »çÇ׿¡ ¸Âµµ·Ï ÇÁ·Î½ÃÀú¸¦ ±¸¼ºÇÏÁö´Â ÀÌ ¿¹Á¦¸¦ ¼öÁ¤ÇÏÁö ¾Ê½À´Ï´Ù.
Excel 1 1900³â ÀÌÀü ³¯Â¥°¡ ÅؽºÆ®·Î ÀԷµ˴ϴÙ. ÀÌ ÇÔ¼ö´Â 1/1/0001·Î ½ÃÀÛÇÏ´Â ÅؽºÆ® ÀÔ·ÂÇÑ ³¯Â¥, Ç¥ÁØ ³¯Â¥ ¹× ¼ö »ç¿ëÇÒ ¶§ ½ÃÀÛ ³¯Â¥¸¦ 1900 Àü¿¡ ÀÖ°í ³¯Â¥ Á¾·á ÈÄ 1900 ³¯Â¥ ó¸®ÇÕ´Ï´Ù. ¸ÅÅ©·Î¸¦ »ç¿ëÇÏ·Á¸é ´ÙÀ½ ÀÌ ´Ü°è¸¦ ¼öÇàÇϽʽÿÀ.

1. ExcelÀ» ½ÃÀÛÇϽʽÿÀ. ÇÔ¼ö¸¦ »ç¿ëÇÏ¿© ¿öÅ©½ÃÆ®¸¦ º¾´Ï´Ù.
2. Visual Basic Editor ÀüȯÇÏ·Á¸é Alt + F11 Å°¸¦ ´©¸¨´Ï´Ù.
3. »ðÀÔ ¸Þ´º¿¡¼­ ¸ðµâ À» Ŭ¸¯ÇϽʽÿÀ.
4. ¸ðµâ¿¡ ´ÙÀ½ Äڵ带 ÀÔ·ÂÇÕ´Ï´Ù:

   ' This is the initial function. It takes in a start date and an end date.
   Public Function AgeFunc(stdate As Variant, endate As Variant)
       
       ' Dim our variables.
       Dim stvar As String
       Dim stmon As String
       Dim stday As String
       Dim styr As String
       Dim endvar As String
       Dim endmon As String
       Dim endday As String
       Dim endyr As String
       Dim stmonf As Integer
       Dim stdayf As Integer
       Dim styrf As Integer
       Dim endmonf As Integer
       Dim enddayf As Integer
       Dim endyrf As Integer
       Dim years As Integer
       
       ' This variable will be used to modify string length.
       Dim fx As Integer
       fx = 0
       
       ' Calls custom function sfunc which runs the Search worksheet function
       ' and returns the results.
       ' Searches for the first "/" sign in the start date.
       stvar = sfunc("/", stdate)
       
       ' Parse the month and day from the start date.
       stmon = Left(stdate, sfunc("/", stdate) - 1)
       stday = Mid(stdate, stvar + 1, sfunc("/", stdate, sfunc("/", stdate) + 1) - stvar - 1)
       
       ' Check the length of the day and month strings and modify the string 
       ' length variable.
       If Len(stday) = 1 Then fx = fx + 1
       If Len(stmon) = 2 Then fx = fx + 1
       
       ' Parse the year, using information from the string length variable.
       styr = Right(stdate, Len(stdate) - (sfunc("/", stdate) + 1) - stvar + fx)
           
       ' Change the text values we obtained to integers for calculation 
       ' purposes.
       stmonf = CInt(stmon)
       stdayf = CInt(stday)
       styrf = CInt(styr)
       
       ' Check for valid date entries.
       If stmonf < 1 Or stmonf > 12 Or stdayf < 1 Or stdayf > 31 Or styrf < 1 Then
           AgeFunc = "Invalid Date"
           Exit Function
       End If
   
       ' Reset the string length variable.
       fx = 0
       
       ' Parse the first "/" sign from the end date.
       endvar = sfunc("/", endate)
       
       ' Parse the month and day from the end date.
       endmon = Left(endate, sfunc("/", endate) - 1)
       endday = Mid(endate, endvar + 1, sfunc("/", endate, sfunc("/", endate) + 1) - endvar - 1)
       
       ' Check the length of the day and month strings and modify the string 
       ' length variable.
       If Len(endday) = 1 Then fx = fx + 1
       If Len(endmon) = 2 Then fx = fx + 1
       
       ' Parse the year, using information from the string length variable.
       endyr = Right(endate, Len(endate) - (sfunc("/", endate) + 1) - endvar + fx)
           
       ' Change the text values we obtained to integers for calculation 
       ' purposes.
       endmonf = CInt(endmon)
       enddayf = CInt(endday)
       endyrf = CInt(endyr)
       
       ' Check for valid date entries.
       If endmonf < 1 Or endmonf > 12 Or enddayf < 1 Or enddayf > 31 Or endyrf < 1 Then
           AgeFunc = "Invalid Date"
           Exit Function
       End If
       
       ' Determine the initial number of years by subtracting the first and 
       ' second year.
       years = endyrf - styrf
       
       ' Look at the month and day values to make sure a full year has passed. 
       If stmonf > endmonf Then
           years = years - 1
       End If
           
       If stmonf = endmonf And stdayf > enddayf Then
           years = years - 1
       End If
   
       ' Make sure that we are not returning a negative number and, if not, 
       ' return the years.
       If years < 0 Then
           AgeFunc = "Invalid Date"
       Else
           AgeFunc = years
       End If
       
   End Function
   
   ' This is a second function that the first will call.
   ' It runs the Search worksheet function with arguments passed from AgeFunc.
   ' It is used so that the code is easier to read.
   Public Function sfunc(x As Variant, y As Variant, Optional z As Variant)
       sfunc = Application.WorksheetFunction.Search(x, y, z)
   End Function
   					

5. ÆÄÀÏÀ» ÀúÀåÇϽʽÿÀ.
6. ´ÙÀ½ µ¥ÀÌÅ͸¦ ÀÔ·ÂÇϽʽÿÀ.
   

   A1   01/01/1887
   A2   02/02/1945
   						

   A3 ¼¿¿¡ ´ÙÀ½ ¼ö½ÄÀ» ÀÔ·ÂÇϽʽÿÀ
   = AgeFunc(startdate,enddate)
   ¿©±â¼­ startdate ù ¹ø° ³¯Â¥ (A1) ¼¿ ÂüÁ¶ÀÎ ¹× enddate µÎ ¹ø° ³¯Â¥ (A2) ¼¿ ÂüÁ¶ÀÔ´Ï´Ù.
   
   °á°ú´Â 58 ÇÕ´Ï´Ù.

Âü°í: 1 1900³â Àü¿¡ ¸ðµç ³¯Â¥¿¡ ´ëÇÑ À¯È¿¼º °Ë»çÇϽʽÿÀ. ³¯Â¥¸¦ ÀÔ·ÂÇÑ ÅؽºÆ® Excel¿¡ ÀÇÇØ È®Àε˴ϴÙ.

ÀÌ ¹®¼­¿¡ ³ª¿Í ÀÖ´Â ¿¹Á¦ Äڵ带 »ç¿ëÇÏ´Â ¹æ¹ý¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ³»¿ëÀº ¾Æ·¡ ¹®¼­ ¹øÈ£¸¦ ´­·¯ Microsoft ±â¼ú ÀÚ·á¿¡ ÀÖ´Â ¹®¼­¸¦ Ŭ¸¯ÇϽʽÿÀ.