hh:mm to decimal time .25

[DBsWifeLB]

How do I get excell hh:mm time translated into decimal form?
I want to be able to say if minutes is between 53 and 7 then round t
00. If 8-22 round to .25 etc.
How would I go about that

 

[firefytr]

Well, you could use IF statments, or a LOOKUP variation. It reall
depends how your data is layed out and what your exact parameters are.
There's not a lot of room for 'etc.'. What exactly are you conditions

 

[Cuda]

I use to use the function below to calculate start/stop time on quarters
when figuring employee time cards.

Function QuarterTime(Val As Date)

Dim Min
Dim Hr

If Val > 0.99999999 Then
QuarterTime = "#VALUE"
Exit Function
End If

Min = Minute(Val)
Hr = Hour(Val)

Select Case Min
Case 53 To 60
QuarterTime = Hr & ".00"
Case 0 To 7
QuarterTime = Hr & ".00"
Case 8 To 22
QuarterTime = Hr & ".25"
Case 23 To 37
QuarterTime = Hr & ".50"
Case 38 To 52
QuarterTime = Hr & ".75"
Case Else
QuarterTime = "#N/A"

End Select

End Function

 

[Cuda]

Sorry, I left the part of the function out that updates the hour for times
between 53 and the hour. The corrected version is below.

Function QuarterTime(Val As Date)

Dim Min
Dim Hr

If Val > 0.99999999 Then
QuarterTime = "#VALUE"
Exit Function
End If

Min = Minute(Val)
Hr = Hour(Val)

Select Case Min
Case 53 To 60
QuarterTime = Hr + 1 & ".00"
Case 0 To 7
QuarterTime = Hr & ".00"
Case 8 To 22
QuarterTime = Hr & ".25"
Case 23 To 37
QuarterTime = Hr & ".50"
Case 38 To 52
QuarterTime = Hr & ".75"
Case Else
QuarterTime = "#N/A"

End Select

End Function

 

[Peo Sjoblom]

Try


=(ROUND(A2/(1/96),0)*1/96)*24

where A2 holds the time

--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[DBsWifeLB]

=(ROUND(A2/(1/96),0)*1/96)*24

where A2 holds the time

This worked perfectly. Thanks so much.
The other options you could not total the columns, each cell came u
showing the correct number but when drilled down they showed a -0
number.

If anyone is looking for my same result, this simple formula works. A
again thanks for all your inputs

 

[Groovel76]

This is a great formula but what if the person just happened to wor
more than 24hrs or if you wish to use this formula to just round th
total hours of someone? I've commented out the If statement yet i
still put #VALUE in the cell if the time has gone over 24:00. I trie
nested IF's to check if Val is between 1.99999999 and 2.99999999 t
subtract 1 from Val and so on all the way up to 5.99999999 but I can'
seem to get it to work. Any help would be greatly appreciated. Thank

 

[Groovel76]

Ok after some tweak and getting used to VB (which I've never used)
think i got it to do what i want which is take the total two week
worth of hours and take that time and round it to the nearest .25
Here is what i came up with. I'm sure it's long winded but it'
currently doing what i need.



Function QuarterTime(Val As Date)

Dim Min
Dim Hr
Dim Extra

Extra = 0

If Val > 0.99999999 And Val <= 1.99999999 Then
Val = Val - 1
Extra = 24
ElseIf Val > 1.99999999 And Val <= 2.99999999 Then
Val = Val - 2
Extra = 48
ElseIf Val > 2.99999999 And Val <= 3.99999999 Then
Val = Val - 3
Extra = 72
ElseIf Val > 3.99999999 And Val <= 4.99999999 Then
Val = Val - 4
Extra = 96
ElseIf Val > 4.99999999 And Val <= 5.99999999 Then
Val = Val - 5
Extra = 120
End If

Min = Minute(Val)
Hr = Hour(Val) + Extra

Select Case Min
Case 53 To 60
QuarterTime = Hr + 1 & ".01"
Case 0 To 7
QuarterTime = Hr & ".00"
Case 8 To 22
QuarterTime = Hr & ".25"
Case 23 To 37
QuarterTime = Hr & ".50"
Case 38 To 52
QuarterTime = Hr & ".75"
Case Else
QuarterTime = "#N/A"

End Select

End Functio