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!
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If all the dates need to be changed as per the value in I4 only, try this...
For Start Time:
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:
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)
=IF(ISNUMBER($I$4),IF($I$4>0,D4+TIMEVALUE($I$4&":00:00"),D4-TIMEVALUE(ABS($I$4)&":00:00")),D4)
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)
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial