Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Adding Time

Posted on 2015-02-22
11
Medium Priority
?
78 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 35

Assisted Solution

by:Norie
Norie earned 400 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
Industry Leaders: 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 1600 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 35

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 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