Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!
Change date/time stamp based on input value
The date/time stamps in column C and D should change based on the + or - value that is inserted into the colored cell in column I
The sheet I am experimenting with is here https://docs.google.com/spreadsheets/d/1q_FcbxZmY9vGRzyggmPQQpinwzH8BFQwQIqBA9Op9SA/edit#gid=1893308266
The sheet I am experimenting with is here https://docs.google.com/spreadsheets/d/1q_FcbxZmY9vGRzyggmPQQpinwzH8BFQwQIqBA9Op9SA/edit#gid=1893308266
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Where would I put the code if don't want to mess up the time/date stamp "formula" that's already in the cells?
You will need to place the formulas in two empty columns in row 4, one for adjusted Start Time and another for adjusted End Time.
Then place the first two formulas in cell J4 and K4 respectively and then copy both the formulas down and you will have two columns with adjusted Start and End Times.
I can't because I would be replacing the function that is already there.
See this attempt at what it appears that you are suggesting.
https://www.screencast.com/t/H2BSByNUfw8B
See this attempt at what it appears that you are suggesting.
https://www.screencast.com/t/H2BSByNUfw8B
Those formulas are referring column C and D respectively so you cannot place them in column C and D because you cannot have values and formulas in the same cells.
As I said you will need to create two new columns with the formulas and those new columns will show you the new start time and new end time.
e.g. in J3 enter "New Start Time" and in K3 enter "New End Time" and then place the formula for Start Time in J4 and copy it down and place the formula for End Time in K4 and copy both the formulas down so that column J will show you the new calculated start time and column K will show you the new calculated End Time.
In case you want to replace the Start Time and End Time in column C and column D itself with the new start time and end time, you will need a macro which will calculate the new start and end time based on the value you have in I4 and place them in column C and D itself.
I am not sure how to do that in Spreadsheet but if you have an Excel Workbook, I can write a macro which can do the same.
As I said you will need to create two new columns with the formulas and those new columns will show you the new start time and new end time.
e.g. in J3 enter "New Start Time" and in K3 enter "New End Time" and then place the formula for Start Time in J4 and copy it down and place the formula for End Time in K4 and copy both the formulas down so that column J will show you the new calculated start time and column K will show you the new calculated End Time.
In case you want to replace the Start Time and End Time in column C and column D itself with the new start time and end time, you will need a macro which will calculate the new start and end time based on the value you have in I4 and place them in column C and D itself.
I am not sure how to do that in Spreadsheet but if you have an Excel Workbook, I can write a macro which can do the same.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialIt's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets
From novice to tech pro — start learning today.
-
Microsoft ApplicationsCertification: IC3 GS4 Internet Core Competency Global Standard 4… 135 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
For Start Time:
Open in new window
For End Time:Open in new window
Otherwise if the Start Times and End Times need to be changed as per the value in column I in their corresponding rows, use the relative reference of I4 so that it would change when you drag the formula down the rows.
To change Start Time and End Time in row4 as per the value in I4 and to change Start Time and End Time in row5 as per the value in I5 (which is blank right now) and so on, try the below formula and drag it down.
For Start Time:
Open in new window
Same way change the formula for End Time also.