Remember Me?

#1
June 18th 15, 04:40 AM
 Junior Member First recorded activity by ExcelBanter: Jun 2015 Posts: 2
Progressing the date one day forward if conditions of time are met:

This is a little complicated for me, so I'm asking for your help. I'm still learning Excel, but I can't seem to figure out the formula for this condition.
- to accurately depict times and dates of checks needed when an incident occurs, starting at the time of the incident. (See attached photo)

Function obtained:
- I've managed to accurately formulate the timing of the schedule. So, the times are all good. All I have to do is set the Time of Incident, and it will automatically change all the times appropriately in the schedule between B10 and B26. This functions well.

Functions needed:
1) Since midnight is random in the schedule, it is impossible to predict when the date will need to be changed in the A column to the next date. If, for example, the incident occurs at 14:20, then I would only want the date on the left of B19 to change by +1 day (in A19), since the time schedule would change to 00:20, as per the scheduled routine.

2) All of the time schedules do not have to be visible if there is no number in Q34. How do I go about making the time schedules in the B column invisible if there is no value in Q34?
Attached Images

#2
June 19th 15, 12:18 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Dec 2012 Posts: 56
Progressing the date one day forward if conditions of time are met:

Here's one approach.

... predict when the date will need to be changed in the
A column to the next date.

Presumably,
- you already have a way to get the start date in A10, and
- the time values in column B don't have the date included invisibly.

In A11, put
=IF(B11=B10,"",MAX(A\$10:A10)+1)
and copy down.

... making the time schedules in the B column invisible
if there is no value in Q34

Since you are satisfied with your formulas in column B, they can be nested within an IF() to accomplish this:
=IF(\$Q\$34="","", [your_formula])
Presumably, the formula in B11 is already copied down to subsequent rows, so the update can proceed the same way, without editing each individual formula.

---

A different approach is to use conditional formatting. That way, the values are still in the cells, but (essentially) written with ink matching the background when needed to make them invisible.
#3
June 19th 15, 06:11 AM
 Junior Member First recorded activity by ExcelBanter: Jun 2015 Posts: 2

Quote:
 Originally Posted by MyVeryOwnSelf[_3_] Presumably, - you already have a way to get the start date in A10,
This date is pulled from a simple =NOW() formula within J34.

Quote:
 Originally Posted by MyVeryOwnSelf[_3_] - the time values in column B don't have the date included invisibly.
That is correct. The formulas just take the time in Q34, and add sequential timing to that value.

Quote:
 Originally Posted by MyVeryOwnSelf[_3_] In A11, put =IF(B11=B10,"",MAX(A\$10:A10)+1) and copy down.
For reasons unknown to me, this method does not work for me. A different approach is to attempt to set a 'range' (for lack of better words) where for example, anything between 00:00 and 00:30 would equal an additional day added to A10. I'd also like to be able to tweak that range, so that it also could cover those rare times when the date would need to change +1 at 01:59 for example if the range was between 00:00 and 01:59. This however is difficult for me because we're not dealing with a value, but a time range.

Quote:
 Originally Posted by MyVeryOwnSelf[_3_] Since you are satisfied with your formulas in column B, they can be nested within an IF() to accomplish this: =IF(\$Q\$34="","", [your_formula])
This worked PERFECTLY - Thanks!!!
A typical Formula for the "B" Column now looks something like this, with the times changed as needed per schedule:
=IF(\$Q\$34="","",B10+TIME(0,30,0)) <----(Example Cell is B11)

Once again, thanks for your help!

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Eclaires Excel Discussion (Misc queries) 8 January 28th 10 11:43 AM Carrie Excel Worksheet Functions 1 September 19th 07 07:32 PM Donna Excel Worksheet Functions 2 June 7th 07 11:22 PM John Sullivan Excel Worksheet Functions 1 April 22nd 06 05:18 PM Thane Excel Worksheet Functions 0 February 2nd 06 05:07 PM

All times are GMT +1. The time now is 05:00 PM.