Converting Time to decimals and then converting decimals to time

[neil_val]

Hi, having a problem with my timeshett system and wondering if anyone
can help??

I have the following in Cells.Range(A5:J8)

Mon Tue Wed Thu Fri Sat Sun Total
Start 08:30 08:30 08:30 08:30 08:30 for
Lunch 01:00 01:00 01:00 01:00 01:00 Week
Finish 17:30 17:30 17:30 17:30 17:30
Total 8.00 8:00 8:00 8:00 8:00 40.0

The following part is the timesheet part where hours are logged
against projects: (Range(A11:L24)

Project No. Work Code Mon Tues Wed Thu Fri Sat Sun
Total Task Category Part Number
1000 08C 8.0 8.0 8.0 8.0
8.0 40.0 PRD 000-000-000


The following is the Totals of the hours done by column:
(Range(A25:J25)

Total For Week 8.0 8.0 8.0 8.0
8.0 40.0

The problem that I am having is that I have VBA that validates the 2
Totals (Range A9:J9) and (Range A25:J25)

I have tried all the conversions from decimal to time and time to
decimal and can not get the timesheet to validate it correctly - ie
the VBA for validation is as follows:

If Val(Sheets(sht).Range("C8").Value) <>
Val(Sheets(sht).Range("Total").Offset(0, 2).Value) Then err = 1

If err = 1 Then

MsgBox ("The totals aren't all equal")

Any help would be greatly appreciated - thanks

 

[Pete_UK]

Your first total for Monday (8.00) is not the same as the other totals
(8:00), but I suspect that you have derived these from your times and
formatted them as [h]:mm, and these are stored internally by Excel as
fractions of a 24-hour day (i.e. 8:00 is actually stored as
0.3333333333 etc). However, the totals for the week seem to be in a
different format (8.0, representing 8 hours), so these are not the
same value. You either have to multiply the time-derived totals by 24,
or divide the other totals by 24 to compare like with like, something
along the lines of:

If Val(Sheets(sht).Range("C8").Value)*24 <>
Val(Sheets(sht).Range("Total").Offset(0, 2).Value) Then err = 1

You might also like to put a bit of tolerance in the comparison,
because if the original times are entered as, say 08:30:01 (i.e. with
seconds) they will not show but will cause problems with your
comparisons. So you could change this to:

If Abs(Val(Sheets(sht).Range("C8").Value)*24 -
Val(Sheets(sht).Range("Total").Offset(0, 2).Value)) > 0.1 Then err = 1

This gives you 1/10 hour tolerance, or 6 minutes.

Hope this helps.

Pete