PDA

View Full Version : subtract 30 minutes and round



paleaux
08-03-2011, 01:11 PM
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.

Baravelli
08-04-2011, 07:45 AM
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
08-04-2011, 08:37 AM
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")
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

paleaux
08-05-2011, 11:18 AM
I will give it a shot and advise.. Thank you for the response I have been working on this for 2 days....

paleaux
08-07-2011, 10:27 AM
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
08-07-2011, 10:33 AM
Post a workbook, give us some example data/results.

cerci
08-07-2011, 05:45 PM
Thank you all

paleaux
08-08-2011, 09:32 AM
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
08-08-2011, 10:24 AM
You are not using a proper time format!

Try this



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")
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

paleaux
08-08-2011, 10:57 AM
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
08-08-2011, 02:12 PM
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.