Change date/time stamp based on input value

Ted Penner
Ted Penner used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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.
Ted PennerSoftware Engineer

Author

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?
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Ted PennerSoftware Engineer

Author

Commented:
It really needs to accept a single input and automatically adjust both columns based on that input.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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.
Ted PennerSoftware Engineer

Author

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
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
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.
Ted PennerSoftware Engineer

Author

Commented:
It would need to be gscript, not excel
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Well I am sorry then as I am not an expert of gscript.
Ted PennerSoftware Engineer

Author

Commented:
No problem.  Thank you for trying.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial