Avatar of Ted Penner
Ted Penner
Flag for United States of America asked on

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
SpreadsheetsMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Ted Penner

8/22/2022 - Mon
Subodh Tiwari (Neeraj)

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 Penner

ASKER
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)

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Ted Penner

ASKER
It really needs to accept a single input and automatically adjust both columns based on that input.
Subodh Tiwari (Neeraj)

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 Penner

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Subodh Tiwari (Neeraj)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Ted Penner

ASKER
It would need to be gscript, not excel
Subodh Tiwari (Neeraj)

Well I am sorry then as I am not an expert of gscript.
Ted Penner

ASKER
No problem.  Thank you for trying.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23