Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Time to decimals and then converting decimals to time
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Converting Time to decimals and then converting decimals to time
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 On Nov 30, 3:21 pm, wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting time to decimals | Excel Worksheet Functions | |||
converting decimals to fractions | Excel Discussion (Misc queries) | |||
converting decimals to fractions | Excel Discussion (Misc queries) | |||
converting decimals to fractions | Excel Discussion (Misc queries) | |||
Converting decimals into hh:mm | Excel Worksheet Functions |