• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 82
  • Last Modified:

Adding Time

EE Pros,

I have two columns.  In one column, I want to put a time in the first cell below a Column Name (Actual Time).....so 8:00am. Then the column next to it, I want to be able to put a time AMOUNT in (such as .30 for 30 minutes or .90 for 90 minutes ..... and so on.  As I put in the time, I need a macro that sees the additional add of time and produces a new time based on the add.   So in my example above, if I start with 8:00am in col. F6 and in column g6 out in .30, the new time in col. F7 will be 8:30am.  

Hopefully this is not too much of a challenge.

Thank you in advance,

B.
Time-and-timing.xls
0
Bright01
Asked:
Bright01
  • 6
  • 3
  • 2
2 Solutions
 
Wilder1626Commented:
Hi

Is that what you are looking for?

Just need to add the timing in minutes in column G and the actual time will populate in column F
Time-and-timing-update.xls
0
 
NorieVBA ExpertCommented:
Do you definitely want the new time in the cell below the original time?

Wouldn't it be better to put it in column H on the same row as the time and increment?

That's what this worksheet event code will do.
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 7 And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        With Target.Offset(, 1)
            .Value = Target.Offset(, -1).Value + Target.Value
            .NumberFormat = "[h]:mm"
        End With
        Application.EnableEvents = True
    End If
End Sub

Open in new window

0
 
Wilder1626Commented:
If you want the time in column F to adjust as you inter minutes in G, you can use this file.
Time-and-timing-update.xls
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Bright01Author Commented:
Neither of these work exactly as I need.  In the first case, I need a way of adding incremental time in a normal format such as 10 min., 30 min........ like .10 or 10 or .3 or 30.  The construct is correct, the way it's entered is not.

As for putting a row view in,.... good idea, but couldn't get it to work with the Macro.

Thanks both for jumping in on this.... hopefully not too complicated.

b.
0
 
Wilder1626Commented:
please have a look at this. Hope this is what you are looking for.
Time-and-timing-update.xls
0
 
Wilder1626Commented:
And in this attachment, just enter the number of minute like: 30 and you will see in the cell 30 min.
Time-and-timing-update.xls
0
 
Bright01Author Commented:
Jean-Marc,

Still, if I want to add 30 minutes, unless I'm reading your Sheet wrong, I have to put in 12:30:00 AM.  I need to be able to put in 30 minutes in some simple format....such as .3 or 30.

Thanks,

B.
0
 
Wilder1626Commented:
are you adding minutes (Column G) to time in Column F or your are counting minutes between to times in Column F?
In my sheet, the time will adjust based on the minutes entered in column G
0
 
Bright01Author Commented:
Great job!

Works well.  Look for the next version request on this later today.

Thanks again,

B.
0
 
Wilder1626Commented:
I'm glad i was able to help. :-)
0
 
NorieVBA ExpertCommented:
If you just want to enter the minutes as 30, 60, 90 etc alter the code I posted to this.
[code]Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 7 And Target.Cells.Count = 1 Then
        Application.EnableEvents = False
        With Target.Offset(, 1)
            .Value = Target.Offset(, -1).Value + TimeSerial(0,Target.Value,0)
            .NumberFormat = "[h]:mm"
        End With
        Application.EnableEvents = True
    End If
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now