Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 18th 15, 04:40 AM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2015
Posts: 2
Default 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.
Purpose of Spreadsheet:
- 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   Report Post  
Old 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
Default 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   Report Post  
Old June 19th 15, 06:11 AM
Junior Member
 
First recorded activity by ExcelBanter: Jun 2015
Posts: 2
Default

Quote:
Originally Posted by MyVeryOwnSelf[_3_] View Post
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_] View Post
- 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_] View Post
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_] View Post
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!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
calculate 6 months forward from a date Eclaires Excel Discussion (Misc queries) 8 January 28th 10 11:43 AM
Timeline Start date forward Carrie Excel Worksheet Functions 1 September 19th 07 07:32 PM
Date going forward Donna Excel Worksheet Functions 2 June 7th 07 11:22 PM
Auto calculate for date + days forward to yield new date John Sullivan Excel Worksheet Functions 1 April 22nd 06 05:18 PM
sum multiple rows based on progressing date field on each row Thane Excel Worksheet Functions 0 February 2nd 06 05:07 PM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017