Mismatch issue

[Seroki]

As part of this I need to round the time to nearest 0,15,30,45 interval. I found a very good script Here : https://msdn.microsoft.com/en-us/lib...(v=vs.60).aspx
My code looks like this:

timestarted = DateValue(Sheet2.Cells(2, 9))


Private Sub CommandButton1_Click()
Dim Ndate, timestarted, timeworked, RTime, WTime As Date

Ndate = NOW

If Ndate > timestarted Then

'length of time worked on a project
timeworked = Ndate - timestarted

'round start up time to nearest 15 to find it on time sheet and mark it
[B][COLOR=#ff0000]RTime = dhRoundTime(timestarted, 15)[/COLOR][/B]

'round time worked to nearest 15 to find it on time sheet and mark it
WTime = dhRoundTime(timeworked, 15)
End If
End Sub

Function dhRoundTime(dtmTime As Date, intInterval As Integer) As Date
' Round the time value in varTime to the nearest minute
' interval in intInterval
Dim intTime As Integer
Dim sglTime As Single
Dim intHour As Integer
Dim intMinute As Integer
Dim lngdate As Long
' Get the date portion of the date/time value
lngdate = DateValue(dtmTime)
' Get the time portion as a number like 11.5 for 11:30.
sglTime = TimeValue(dtmTime) * 24
' Get the hour and store it away. Int truncates,
' CInt rounds, so use Int.
intHour = Int(sglTime)
' Get the number of minutes, and then round to the nearest
' occurrence of the interval specified.
intMinute = CInt((sglTime - intHour) * 60)
intMinute = CInt(intMinute / intInterval) * intInterval
' Build back up the original date/time value,
' rounded to the nearest interval.
'changed this line to return only the rounded time needed

dhRoundTime = CDate((intHour + intMinute / 60) / 24)

'orginal line below
'dhRoundTime = CDate(lngdate + ((intHour + intMinute / 60) / 24))

End Function

The highlighted section above is giving me an error.
I think it may be due to the format of my dhRoundTime(timestarted, 15) being a Variable instead of a dhRoundTime(#12:32:15#, 15)

This is the content of the cell for timestarted "7/9/2015 8:51:42 AM"

Thanks for looking

 

[Luke M]

In your variable definition line, (and it's a common mistake), only the last variable is actually getting defined as a Date. All the rest are undefined variants. Correct line would read

Dim Ndate As Date, timeStarted As Date, timeWorked As Date, RTime As Date, WTime As Date

 

[Seroki]

That was it!!! thank you very much.

 

[Seroki]

Next issue: in my excel sheet I put in times going down a column from 6 to 6 at 15 min intervals. when I reference these date i am not getting a date time im getting .75,1.00,1.25,... I am using these in a comparison to a NOW timestamp.
is there a way to either fix excel to give true time or a way to convert the decimal offset to a time?

 

[Luke M]

I'm not sure what you mean...can you upload an example?

 

[Seroki]

in sheet "wk28" the column 3 is a set of incremented time. in my form during stop tracking time privet function FindRow i do a compare look at that column and a NOW timestamp NTime

Private Function FindRow(x As Date)
Dim irow, iCounter As Integer
Dim sTime As Date

iCounter = 57
For Z = 9 To iCounter
    With Sheet3
    sTime = Cells(Z, 3)
    End With
      
    If sTime = x Then
        FindTime = Z
    End If
Next
End Function

sTime is returning a .75 instead of "6:45 am"
so when I compare x to sTime it never matches.
i need column 3 to each be a time not a increment or i need to be able to convert .75 to 6:45 am....

 

[Seroki]

In general i am hacking this together we such a mix of coding styles it is becoming a train wreck.. but it is starting to come together. slowly.

Thanks for all your help.

 

[Luke M]

I think the biggest problem was in your function you had this line

FindTime = Z

but FindTime is not the name of the function...it's nothing really.

The other bit is that you're right, XL seems to get trouble with the tiny decimals in the time values...I changed the FindRow function to compare the string format of "hhmm". This forces any little decimals to get chopped off, and we get a good comparison.

 

[Seroki]

that was the issue.
now i get this error when entering time. it is using almost the same code and stopping time.






I checked the values. all seem to be good not sure what it is freaking out about now.
adding in my updated file clean up a bit of the confusion.

 

[Luke M]

Oops, I forgot to mention that the LastColumn function is wrong as well. Old code with comments:

Public Function FindColumn(v As Variant)
'Note that our input is v...
Dim icolumn As Integer, LWeekday As Long

'We never gave a value to LWeekday, so it equals 0
  Select Case LWeekday
    Case 1
        FindColumn = 4
    Case 2
        FindColumn = 5
    Case 3
        FindColumn = 6
    Case 4
        FindColumn = 7
    Case 5
        FindColumn = 8
    Case 6
        FindColumn = 40
  End Select
'and so our function does nothing
End Function

Corrected:

Public Function FindColumn(v As Variant)

  Select Case v
    Case 1
        FindColumn = 4
    Case 2
        FindColumn = 5
    Case 3
        FindColumn = 6
    Case 4
        FindColumn = 7
    Case 5
        FindColumn = 8
    Case 6
        FindColumn = 40
  End Select
End Function

 

[Seroki]

Fixed it i was not using the correct variable in my CASE function.

 

[Seroki]

Ok got it working !!!

THANKS SO MUCH FOR THE CONTINUED HELP!

now it is time to clear data off the data sheet and keep it in the Code.

Private Function CProject(project As String)
' vault the Current Project here untill a new one is chosen
Dim saveproject As String
If saveproject = "" Or saveproject <> project Then
saveproject = project
End If
CProject = project
End Function

Private Function CurrentDate(CPDate As Date)
' vault the Current Project Date here untill a new one is chosen
Dim savedate As Date
If savedate = "" Or savedate <> CPDate Then
savedate = CPDate
End If
CurrentDate = savedate
End Function

so I have been sending this data to a cell location on my sheets("DataSheet")

now that I have stored it how do I use it ?

timestarted = CurrentDate()

?

 

[Luke M]

Not 100% sure what you're asking...I will say, IMO, if you're wanting to store information about start time for some sort of timer, it's easier and more reliable to store the information in a cell. You can store the information within a global variable, but it's still possible for the information to get lost.

 

[Seroki]

I understand.
Thank you.