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
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