PDA

View Full Version : Returning a date with a function



FDomingos
06-07-2013, 09:01 AM
Hi,

I need to make an Excel Function which giving to times (entry time and exiting time), it calculates the time (hh:mm) between these two, for example if the employee entries at 23:00 (11pm), and exits at 07:00 (7am), it should write 8:00 in the cell.
The reason that I'm not using time subtract is because of cases like the one in the previous example, it will give and error.

So far, I did this:

Function DIFERENCAHORASMINUTOS(ByVal HoraEntrada As Date, ByVal HoraSaida As Date)
Dim hsaida, hentrada, msaida, mentrada, dminutos, dhoras As Integer
hsaida = Int(Hour(HoraSaida))
hentrada = Int(Hour(HoraEntrada))
mentrada = Int(Minute(HoraEntrada))
msaida = Int(Minute(HoraSaida))
If HoraSaida < HoraEntrada Then
If msaida < mentrada Then
dhoras = (hsaida + 24) - hentrada
dminutos = (msaida + 60) - mentrada
DIFERENCAHORASMINUTOS = Time(dhoras, dminutos, 0)
Else
dhoras = (hsaida + 24) - hentrada
dminutos = msaida - mentrada
DIFERENCAHORASMINUTOS = Time(dhoras, dminutos, 0)
End If
Else
If msaida < mentrada Then
dhoras = hsaida - hentrada
dminutos = (msaida + 60) - mentrada
DIFERENCAHORASMINUTOS = Time(dhoras, dminutos, 0)
Else
dhoras = hsaida - hentrada
dminutos = msaida - mentrada
DIFERENCAHORASMINUTOS = Time(horas, dminutos, 0)
End If
End If
End Function


But this gives me a #value error.
Sorry, about the vars not in English, but if you want, I translate them.
HoraEntrada is the time which the employee enters the job;
HoraSaida is the time which the employee exits the job;

Thanks a lot for your attention,

Best Regards

SamT
06-07-2013, 09:19 AM
This might work
Function DIFERENCAHORASMINUTOS(ByVal HoraEntrada As Date, ByVal HoraSaida As Date) As String
DIFERENCAHORASMINUTOS = Format(HoraSaida - HoraEntrada, "[h]:mm")
End Function