Jak vypo��tat v�k p�ed 1/1/1900 v aplikaci Excel

A�koli uzav�en� datum vzorce lze pou��t pouze data zadan� mezi 1, 1/1900 do 31/12/9999 m��ete pou��t vlastn� Microsoft Visual Basic pro Applications funkci Vypo��tat st��� (v letech) osoby �i objektu, kter� byl vytvo�en nejprve p�ed 1. ledna 1900.

V�po�et st��� pou��t makro

Spole�nost Microsoft poskytuje uk�zky programov�n� pouze pro ilustraci, bez ��dn� z�ruky v�slovn� uveden� nebo odvozen�,. v�etn�, bez omezen�, odvozen�ch z�ruk vztahuj�c�ch se k obchodovatelnosti nebo vhodnosti pro ur�it� ��el. Tento �l�nek p�edpokl�d�, �e u�ivatel je obezn�men s programovac�m jazykem, kter� je p�edm�tem uk�zky, a s n�stroji pou�it�mi pro vytvo�en� a lad�n� skriptu. Pracovn�ci technick� podpory spole�nosti Microsoft mohou vysv�tlit funkce ur�it�ho postupu, nemohou v�ak n�sleduj�c� p��klady roz���it o dal�� funkce nebo konstrukce podle konkr�tn�ch po�adavk� u�ivatele.
Excel zad� data p�ed 1, 1/1900 jako text. Tato funkce funguje pro kalend��n� data zadan� jako text za��naj�c� 1, 1/0001, norm�ln� kalend��n� data a m��e zpracov�vat data, kdy je p�ed 1900 po��te�n� datum a koncov� datum je po 1900. Chcete-li pou��t makro, postupujte takto:

1. Spus�te aplikaci Excel. Zobrazen� listu, kter� chcete pou��t funkci.
2. Stiskn�te ALT + F11 p�epn�te do editoru jazyka.
3. V nab�dce Vlo�it klepn�te na modul.
4. V modulu zadejte n�sleduj�c� k�d:

   ' 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. Ulo�te soubor.
6. Zadejte n�sleduj�c� data:
   

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

   Zadejte n�sleduj�c� vzorec v bu�ce A3
   = AgeFunc(startdate,enddate)
   kde startdate je odkaz na bu�ku datum prvn� (A1) a enddate je odkaz na bu�ku druh�ho data (A2).
   
   V�sledek by m�l b�t 58.

Pozn�mka: Zkontrolujte platnost v�echna data p�ed 1, 1/1900. Data zadan� jako text nejsou kontrolov�ny aplikac� Excel.

Dal�� informace o zp�sobu pou�it� uk�zkov�ho k�du v tomto �l�nku naleznete v n�sleduj�c�m �l�nku znalostn� b�ze Microsoft Knowledge Base: