vba rounding

[paleaux]

Using the code below and it works pefect but what I really need to have happen is that when I run the code and it updates on the "Timesheet"
I need to automatically deduct 30 minutes and then round up or down accordingly.

example: 6.31-.30 = 6.01 = 6.00

7.55 - .30 = 7.25 = 7.30


Sub Test()
Worksheets("Gate_Log").Range("A2:B300").Copy Worksheets("Timesheet").Range("B2")
Worksheets("Gate_Log").Range("D2:D300").Copy Worksheets("Timesheet").Range("D2")
Worksheets("Gate_Log").Range("E2:E300").Copy Worksheets("Timesheet").Range("L2")
End Sub


I have been working on this all day I must be dumber than I thought

All help will be very much appreciated..

 

[paleaux]

bump.... any takers on this one ...

Thanks,

 

[shg]

I think no one's jumping in because your requirement is very vague. Are your times Excel time (not by your example), or decimal hours, or does 6.30 mean 6:30?

Round the results to the nearest 15 minutes, half hour, ...?

Try to provide enough information that the question only needs to be answered once.

 

[paleaux]

Apologies, you are correct it is vague...long day !!

The return from the current vba (red text) is total number of hours on the gate log.
What I am trying to do is, when I run the code and it copies the data in to the Timesheet, it will automatically deduct 30 minutes from the total number of hours and then round that number to the nearest hour.

Example: Total Hours before code execution = 7.55
Total Hours after code execution= 7.25, rounded to 7.00.

Total Hours before code execution= 8.00
Total Hours after code execution= 7.30 rounded to 8.00

Thank you for helping, again apologies for being vague....

 

[paleaux]

Ok making progress but need a little more guidance, please..
Have vba that copies a certain criteria from Gate Log rounds the total hours worked and then paste the data into the Timesheet
see code below:

Sub Test()
Worksheets("Gate_Log").Range("A2:B300").Copy Worksheets("Timesheet").Range("B2")
Worksheets("Gate_Log").Range("D2:D300").Copy Worksheets("Timesheet").Range("D2")
Worksheets("Gate_Log").Range("E2:E300").Copy Worksheets("Timesheet").Range("L2")
auto deduct 30 minutes here,
Dim OneCell As Range
For Each OneCell In Range(Range("L2"), Range("L" & Rows.Count).End(xlUp))
OneCell.Formula = "=Round(" & OneCell.Value & ",0)"
Next OneCell
End Sub


what I need is to have the code automatically deduct 30 minutes from the total hours on the gate log and then round it to the nearest hour based on 30 minute scale and then paste that value in column L of Timesheet. Current code works great but is rounding up or down based on .50 scale wrather than .30 ,ie; 30 minutes.

Example:
Gate Log = 7.45 - 30 minutes = 7.15 then rounded = 7.00

any help would be greatly appreciated.

 

[paleaux]

just a little help..

I hope Iam expalining this in enough detail, if not please let me know and I
will try again...
once again any help will be very much appreciated...

Thanks,

 

[paleaux]

bump.... anybody !!!

 

[paleaux]

well it appears that I have no takers on this one, maybe next time.
Have a nice day !!!!