- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Need some Excel help
Posted on 1/13/15 at 9:20 pm
Posted on 1/13/15 at 9:20 pm
I have a form that automatically updates a Google spreadsheet with a time value. I need another column to simply add 29 minutes to that time value in the corresponding row of the other column.
For example:
Start time____________________________End Time
__6:00 pm_______________________(need 6:29 pm to show here)
There is no limit, I just need the second time to show up automatically after the initial time is added to the calendar. I do know that to add time you use +Time(Hr,Min,Sec)
An easier example without time is simply
Number____________________________Number and 3
___2__________________________(I would need 5 to show up here)
I can do it for each row manually, but I need it to be done automatically soon as a new row is added from the form.
For example:
Start time____________________________End Time
__6:00 pm_______________________(need 6:29 pm to show here)
There is no limit, I just need the second time to show up automatically after the initial time is added to the calendar. I do know that to add time you use +Time(Hr,Min,Sec)
An easier example without time is simply
Number____________________________Number and 3
___2__________________________(I would need 5 to show up here)
I can do it for each row manually, but I need it to be done automatically soon as a new row is added from the form.
This post was edited on 1/13/15 at 9:24 pm
2
Posted on 1/13/15 at 9:38 pm to Guess
You'll need a macro with auto fill script or copy a "if"statement for the entire column.
Posted on 1/14/15 at 12:05 am to chrisman17
=A1+TIME(0,29,0)
A1 is the start time and then the formula adds the 29 minutes. Drag that formula down for all time entries in column A and 29 minutes will be added to each associated row in column A.
Does this do what you need? Unclear on exactly the output you are looking for.
A1 is the start time and then the formula adds the 29 minutes. Drag that formula down for all time entries in column A and 29 minutes will be added to each associated row in column A.
Does this do what you need? Unclear on exactly the output you are looking for.
Posted on 1/14/15 at 12:18 am to lynxcat
quote:
=A1+TIME(0,29,0)
Unless I'm missing something, that only gives the correct answer in B1. Any additions to column A give nothing.
I tried B2:B1000000 as the range in the top left box and used =A2:A1000000+Time(0,29,0), but that still only gives the correct answer in B2.(note I'm starting from row 2 as the first row is reserved as the title from the form.)
Posted on 1/14/15 at 12:23 am to chrisman17
quote:
You'll need a macro with auto fill script or copy a "if"statement for the entire column.
Can you give an example of what that would look like?
Posted on 1/14/15 at 2:58 am to Guess
Ok, I used =ARRAYFORMULA(A2:A+Time(0,29,0)) in B2 and it works pretty well. Anytime a time or date and time is added to column A, I automatically get the correct result in column B. Now the problem I have is that column B is filled with results based off of 0 even if nothing is in column A. I need Column B to stay empty unless Column A has a date or date and time.
ETA: I asked for Excel help, but I'm realizing Google Spreadsheets is a little different from both Open Office and Excel.
ETA: I asked for Excel help, but I'm realizing Google Spreadsheets is a little different from both Open Office and Excel.
This post was edited on 1/14/15 at 3:52 am
Posted on 1/14/15 at 8:08 am to Guess
=IF(A3="","",A3+TIME(0,29,0))
works in excel for me. You have to drag it down indefinitely through how far you expect the table to extend.
works in excel for me. You have to drag it down indefinitely through how far you expect the table to extend.
Posted on 1/14/15 at 3:26 pm to Guess
quote:
Posted by Guess
Ok, I used =ARRAYFORMULA(A2:A+Time(0,29,0)) in B2 and it works pretty well. Anytime a time or date and time is added to column A, I automatically get the correct result in column B. Now the problem I have is that column B is filled with results based off of 0 even if nothing is in column A. I need Column B to stay empty unless Column A has a date or date and time.
ETA: I asked for Excel help, but I'm realizing Google Spreadsheets is a little different from both Open Office and Excel.
This is why I suggested the "if" statement. Just plug in if and the value if 0=" ""
Posted on 1/14/15 at 8:00 pm to TigerFanatic99
quote:
You have to drag it down indefinitely through how far you expect the table to exten
You can also put that column next to one that's already filled in with data. That way you can just double-click the lower right hand corner of the first cell and autofill.
Posted on 1/14/15 at 11:13 pm to Bestbank Tiger
Or just press Ctrl shift down and paste the formula for the entire column 
I'm still not sure why my formula would not work. I don't think I fully understand what you are trying to accomplish.
Enter time in column A and then each time an additional row time is entered you want to add 29 to the corresponding B column...this does not seem difficult.
I'm still not sure why my formula would not work. I don't think I fully understand what you are trying to accomplish.
Enter time in column A and then each time an additional row time is entered you want to add 29 to the corresponding B column...this does not seem difficult.
Posted on 1/15/15 at 12:17 pm to Guess
You have to create a Macro.
If your Developer Tab is not visible, add it by customizing the toolbar ribbon under "File:Options". Once you have the developer ribbon displayed, then simply click on Developer->Insert->Form Control->Button
It will popup with the list of Macros to assign to the button. Select the macro you want. The one I have above basically copies the active row and inserts it directly below. So basically, each new row is an exact duplicate of whatever row is currently active.
You can dress this up by specifying the active range to copy from -- that way instead of relying on the user to have the "right row" highlighted, it always copies the same row and inserts it into the same place in the sheet.
quote:
Sub macroNewRow()
ActiveCell.EntireRow.Select
Selection.Copy
Selection.Insert Shift:=xlDown
End Sub
If your Developer Tab is not visible, add it by customizing the toolbar ribbon under "File:Options". Once you have the developer ribbon displayed, then simply click on Developer->Insert->Form Control->Button
It will popup with the list of Macros to assign to the button. Select the macro you want. The one I have above basically copies the active row and inserts it directly below. So basically, each new row is an exact duplicate of whatever row is currently active.
You can dress this up by specifying the active range to copy from -- that way instead of relying on the user to have the "right row" highlighted, it always copies the same row and inserts it into the same place in the sheet.
Posted on 1/15/15 at 3:37 pm to Floating Change Up
I ended up using =IF(ISBLANK(A2),"",A2+Timel(0,29,0)) and dragged just dragged it down.
Page 1 of 1
Popular
Back to top
Follow TigerDroppings for LSU Football News