subtract 30 minutes and round

[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:

[VBA]Sub Test()
Worksheets("Gate_Log").Range("A2:B300").Copy Worksheets("Timesheet").Range("B2")
Worksheets("Gate_Log").Range("D2300").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[/VBA]


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.

[Baravelli]

the best thing to do would be to back up and use date and time values when pulling data from the spreadsheet.

For example, if you wish to use an "hour" cell and a "minute" cell then
A1: 8
B1: 15
C1: =TIMEVALUE(A1&":" &B1)
The value of C1 will be a decimal. 1 represents exactly 1 day. 1/24 is one hour and so on. You can add or subtract these values easily.

[Bob Phillips]

[vba]

Sub Test()
Worksheets("Gate_Log").Range("A2:B300").Copy Worksheets("Timesheet").Range("B2")
Worksheets("Gate_Log").Range("D2300").Copy Worksheets("Timesheet").Range("D2")
Worksheets("Gate_Log").Range("E2:E300").Copy Worksheets("Timesheet").Range("L2")
Dim OneCell As Range
For Each OneCell In Range(Range("L2"), Range("L" & Rows.Count).End(xlUp))
OneCell.Value = Application.Round((OneCell.Value - TimeSerial(0, 30, 0)) * 48, 0) / 48
Next OneCell
End Sub[/vba]

[paleaux]

I will give it a shot and advise.. Thank you for the response I have been working on this for 2 days....

[paleaux]

Sorry doesn't appear to work Gate Log "L2" is formatted as a number,
vba is only rounding by 3 minutes.
also I am not very sure how I can deduct 30 minutes.
Thank you all very much for the help.
If you can offer just a little more I would sure appreciate it.

[Bob Phillips]

Post a workbook, give us some example data/results.

[cerci]

Thank you all

[paleaux]

Workbook needs to subtract 30 minutes from gate log total hours
VBA button hidden in $ sign on ribbon.
The total # of hours on the gate log is a numeric value not a time value
So whatever the total is needs to have 30 minutes (.30) subtracted and then needs to be rounded to the nearest whole # based off of 15 minute intervals.

Example: 6.55 - .30 = 6.25 (Rounded to 6.30)
7.15 - .30 = 6.45 (Rounded to 7.00)
8.14 - .30 = 7.44 (Rounded to 7.30)

By rounding like this will ensure that all values will be displayed as either
hour:30 minutes or hour:00.
No 1/4 hour calculations will be necessary.. ie; 7:15 etc...

workbook attaced...

Thanks again for the help..

[Bob Phillips]

You are not using a proper time format!

Try this

[vba]

Sub Test()
Worksheets("Gate_Log").Range("A2:B300").Copy Worksheets("Timesheet").Range("B2")
Worksheets("Gate_Log").Range("D2300").Copy Worksheets("Timesheet").Range("D2")
Worksheets("Gate_Log").Range("E2:E300").Copy Worksheets("Timesheet").Range("L2")
Dim OneCell As Range
For Each OneCell In Range(Range("L2"), Range("L" & Rows.Count).End(xlUp))
OneCell.Value = Application.Round(((Int(OneCell.Value) + (OneCell.Value - Int(OneCell.Value) - 0.3) * 100 / 60) / 24) * 48, 0) / 48
Next OneCell
End Sub
[/vba]

[paleaux]

Are you saying it should be formatted as military time ?
Tried code but returns a value of 0.3

If 6.55-.30=6.25 (Rounded=6.30

I really appreciate your help on this...

[Bob Phillips]

I am saying time is like this

6:55

not like this

6.55

That is why my original code failed to work because you weren't using time formats. My latest code should work regardless, changing it to time.