Keeping records for working hours with excel
 

Hi

I've made working hour record keeping tool with excel. This is how it
looks. It works well,
but I would like to know how it would be possible to create easier.

DATE DAY HOURS1(E) HOURS2(G) SALDO1(H) SALDO2(I)
-----------------------------------------------------------------
21.3.2007 Ke 6:06 6,10 -36,90 -36:54


My sheet is used by typing in HOURS1 value, which is actual working
hours in
format HOURS:MINUTES.

HOURS2 is created by excel using formula:
=IF(E79="";"";HOUR(E79)+MINUTE(E79)/60)

SALDO1 using formula(7,5 is my normal working hours):
=IF(E79="";"";H76+G79-7,5)

SALDO2 using formula:
=IF(H79="";"";IF(H79<0;"-"&INT(ABS(H79))&":"&IF(LEN(INT((ABS(H79)-INT(ABS(H79)))*60))=1;"0"&INT((ABS(H79)-INT(ABS(H79)))*60);INT((ABS(H79)-INT(ABS(H79)))*60));INT(ABS(H79))&":"&IF(LEN(INT(( ABS(H79)-INT(ABS(H79)))*60))=1;"0"&INT((ABS(H79)-INT(ABS(H79)))*60);INT((ABS(H79)-INT(ABS(H79)))*60))))

Is there easier way to convert time in decimal format back to time
format. Even if that time is
negative value.

Thanks in advance.

Keeping records for working hours with excel
 

=IF(H79<0;"-";"")&TEXT(ABS(H79)/24;"[h]:mm;;;")

On 26 Mar, 07:32, Lauri Kelo wrote:
Hi

I've made working hour record keeping tool with excel. This is how it
looks. It works well,
but I would like to know how it would be possible to create easier.

DATE DAY HOURS1(E) HOURS2(G) SALDO1(H) SALDO2(I)
-----------------------------------------------------------------
21.3.2007 Ke 6:06 6,10 -36,90 -36:54

My sheet is used by typing in HOURS1 value, which is actual working
hours in
format HOURS:MINUTES.

HOURS2 is created by excel using formula:
=IF(E79="";"";HOUR(E79)+MINUTE(E79)/60)

SALDO1 using formula(7,5 is my normal working hours):
=IF(E79="";"";H76+G79-7,5)

SALDO2 using formula:
=IF(H79="";"";IF(H79<0;"-"&INT(ABS(H79))&":"&IF(LEN(INT((ABS(H79)-INT(ABS(H*79)))*60))=1;"0"&INT((ABS(H79)-INT(ABS(H79)))*60);INT((ABS(H79)-INT(ABS(H79*)))*60));INT(ABS(H79))&":"&IF(LEN(INT( (ABS(H79)-INT(ABS(H79)))*60))=1;"0"&I*NT((ABS(H79)-INT(ABS(H79)))*60);INT((ABS(H79)-INT(ABS(H79)))*60))))

Is there easier way to convert time in decimal format back to time
format. Even if that time is
negative value.

Thanks in advance.

Keeping records for working hours with excel
 

To convert a time in hh:mm to decimal just multiply by 24 and fomat cell as
GENERAL.

A1=07:30
B1=A1*24 (=7.5)

HTH

"Lauri Kelo" wrote:

Hi

I've made working hour record keeping tool with excel. This is how it
looks. It works well,
but I would like to know how it would be possible to create easier.

DATE DAY HOURS1(E) HOURS2(G) SALDO1(H) SALDO2(I)
-----------------------------------------------------------------
21.3.2007 Ke 6:06 6,10 -36,90 -36:54


My sheet is used by typing in HOURS1 value, which is actual working
hours in
format HOURS:MINUTES.

HOURS2 is created by excel using formula:
=IF(E79="";"";HOUR(E79)+MINUTE(E79)/60)

SALDO1 using formula(7,5 is my normal working hours):
=IF(E79="";"";H76+G79-7,5)

SALDO2 using formula:
=IF(H79="";"";IF(H79<0;"-"&INT(ABS(H79))&":"&IF(LEN(INT((ABS(H79)-INT(ABS(H79)))*60))=1;"0"&INT((ABS(H79)-INT(ABS(H79)))*60);INT((ABS(H79)-INT(ABS(H79)))*60));INT(ABS(H79))&":"&IF(LEN(INT(( ABS(H79)-INT(ABS(H79)))*60))=1;"0"&INT((ABS(H79)-INT(ABS(H79)))*60);INT((ABS(H79)-INT(ABS(H79)))*60))))

Is there easier way to convert time in decimal format back to time
format. Even if that time is
negative value.

Thanks in advance.