Solved

Adding Time

Posted on 2015-02-22
11
72 Views
Last Modified: 2015-02-22
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
Comment
Question by:Bright01
  • 6
  • 3
  • 2
11 Comments
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624183
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
 
LVL 33

Assisted Solution

by:Norie
Norie earned 100 total points
ID: 40624196
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
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624200
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Bright01
ID: 40624203
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
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624211
please have a look at this. Hope this is what you are looking for.
Time-and-timing-update.xls
0
 
LVL 11

Accepted Solution

by:
Wilder1626 earned 400 total points
ID: 40624220
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
 

Author Comment

by:Bright01
ID: 40624227
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
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624232
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
 

Author Closing Comment

by:Bright01
ID: 40624236
Great job!

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

Thanks again,

B.
0
 
LVL 11

Expert Comment

by:Wilder1626
ID: 40624237
I'm glad i was able to help. :-)
0
 
LVL 33

Expert Comment

by:Norie
ID: 40624266
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question