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
frugalmuleAsked:
Who is Participating?
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.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
If all the dates need to be changed as per the value in I4 only, try this...

For Start Time:
=IF(ISNUMBER($I$4),IF($I$4>0,C4+TIMEVALUE($I$4&":00:00"),C4-TIMEVALUE(ABS($I$4)&":00:00")),C4)

Open in new window

For End Time:
=IF(ISNUMBER($I$4),IF($I$4>0,D4+TIMEVALUE($I$4&":00:00"),D4-TIMEVALUE(ABS($I$4)&":00:00")),D4)

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:
=IF(ISNUMBER(I4),IF(I4>0,C4+TIMEVALUE(I4&":00:00"),C4-TIMEVALUE(ABS(I4)&":00:00")),C4)

Open in new window

Same way change the formula for End Time also.
0
frugalmuleAuthor Commented:
Where would I put the code if don't want to mess up the time/date stamp "formula" that's already in the cells?
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
0
Determine the Perfect Price for Your IT Services

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!

frugalmuleAuthor Commented:
It really needs to accept a single input and automatically adjust both columns based on that input.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
0
frugalmuleAuthor Commented:
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
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
0

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 trial
frugalmuleAuthor Commented:
It would need to be gscript, not excel
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Well I am sorry then as I am not an expert of gscript.
0
frugalmuleAuthor Commented:
No problem.  Thank you for trying.
0
It'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.