Date anteriori al 1900, Excel per Mac

Question

E' possibile gestire le date anteriori al 1900 usando Excel per Mac. Ho visto che viene suggerito di cliccare ALT F11 e poi inserire un programmino in Visual Basic, ma non mi pare possibile sul Mac.

Grazie

Renato Fianco

Answer 1

5- Salva il file.

6- Immettere i seguenti dati:

A1 01/01/1887

A2 02/02/1945

Nella cella A3, inserisci la seguente formula:

=AgeFunc(startdate,enddate)

La data di inizio è un riferimento di cella alla prima data (A1) e la data di fine è un riferimento di cella alla seconda data (A2).

Il risultato dovrebbe essere 58.

Answer 2

' 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

Answer 3

1 - Avvia Excel. Visualizza il foglio di calcolo su cui desideri utilizzare la funzione.

2- Premere ALT + F11 per passare a Visual Basic Editor.

3- Nel menu Inserisci, fare clic su Modulo.

4- Immettere il seguente codice nel modulo: