We help IT Professionals succeed at work.

Change date/time stamp based on input value

178 Views
Last Modified: 2018-09-11
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

Subodh Tiwari (Neeraj)Excel & VBA Expert
CERTIFIED 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 PennerData Structures 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
CERTIFIED 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.
Ted PennerData Structures 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
CERTIFIED 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 PennerData Structures 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
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Ted PennerData Structures Engineer

Author

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

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

Author

Commented:
No problem.  Thank you for trying.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.