Using Times to make an IF statement

[hejman08]

I have a spreadsheet set up with Column B having a Start Time, Column C having a Duration, Column D having a calculated End time based on B and C. I then have 7 columns of "does this backup happen on this day of the week?" with Yes or No in the field. Then I have a 12:00am, 1:00am, ... 11:00pm Sunday and repeating through Saturday, one hour in each column, 24 hours for each Day of the week. What I want to do, using the example of Monday at 3am:
In the Monday at 3:00am column:
if the Monday cell is set to Yes for this row, AND 3:00am falls between the Start and End times for this backup, Display an X in the Monday 3:00am field, if not do nothing.
I have tried messing with TIME functions and If then statements (which I fully and freely admit that the more nested IF statements you have the more I suck at writing them) and just cannot make this work for me. Any suggestions would be appreciated.

~nathan

 

[J.Ty.]

Hi,

Welcome to MrExcel and a few questions to clarify your needs.

1) How long can a single event last? Can it span more than 1 hour, 1 day, etc.?
2) If it begins at 11:59PM and Duration is 3 minutes do you want to have two "X" ticks, or just one (and which one)?
3) If it begins on Sunday at 11:59PM and Duration is 3 minutes, do you then have two "Yes" entries in the days of the week: one for Sunday and one for Monday, and if not, which one do you have?
4) If it begins on Sunday at 11:59PM and Duration is 3 hours, how many "Yes" entries do you have in the days of the week?
5) If it begins on Sunday at 11:59PM and Duration is 3 hours, how many "X" ticks do you want to have?

J.Ty.

 

[hejman08]

Thank you for your kind welcome. My responses to your questions are below. Thanks for your help!

1) Most events run about 8 hours, but as little as 1.5 hours and as much as 18 hours.
2) If it begins at 11:59PM and Duration is 3 minutes I just want there to be a tick in the 11:00pm and 12:00am fields for the day(s) in which a Yes exists, since it falls between those times
3) If it begins on Sunday at 11:59PM and Duration is 3 minutes, do you then have two "Yes" entries in the days of the week: one for Sunday and one for Monday, and if not, which one do you have? I would have 1 tick for the Sunday 11:00pm and 1 for Monday 12:00am since it falls between those two hours
4) If it begins on Sunday at 11:59PM and Duration is 3 hours, how many "Yes" entries do you have in the days of the week? The yes entries correspond to the start times, meaning that the sunday column would have a Yes and unless it begins again on Monday at that time, it would be a No
5) If it begins on Sunday at 11:59PM and Duration is 3 hours, how many "X" ticks do you want to have? 1 tick in each of the fields for Sunday 11:00pm, Monday 12:00am, Monday 1:00am, Monday 2:00am

Basically the end result I want is "Is this backup running on this day at this time? If so, put an X in the cell where that backup's row meets that time's column
It may also help, here are the first 2 rows of my sheet with the header names:
[TABLE="width: 8679"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col span="9"><col span="3"><col span="9"><col span="3"><col span="9"><col span="3"><col span="9"><col span="3"><col span="9"><col span="3"><col span="9"><col span="3"><col span="9"><col span="3"><col span="9"><col span="3"><col span="9"><col span="3"><col span="9"><col span="3"><col span="9"><col span="3"><col span="9"><col span="3"><col span="9"><col span="3"><col span="9"><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 24"]Sunday[/TD]
[TD="colspan: 24"]Monday[/TD]
[TD="colspan: 24"]Tuesday[/TD]
[TD="colspan: 24"]Wednesday[/TD]
[TD="colspan: 24"]Thursday[/TD]
[TD="colspan: 24"]Friday[/TD]
[TD="colspan: 24"]Saturday[/TD]
[/TR]
[TR]
[TD]NAME of backup[/TD]
[TD]Start Time [/TD]
[TD]Duration[/TD]
[TD]End Time[/TD]
[TD]Sunday[/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD]Wednesday[/TD]
[TD]Thursday[/TD]
[TD]Friday[/TD]
[TD]Saturday[/TD]
[TD="align: right"]12:00 AM[/TD]
[TD="align: right"]1:00 AM[/TD]
[TD="align: right"]2:00 AM[/TD]
[TD="align: right"]3:00 AM[/TD]
[TD="align: right"]4:00 AM[/TD]
[TD="align: right"]5:00 AM[/TD]
[TD="align: right"]6:00 AM[/TD]
[TD="align: right"]7:00 AM[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]9:00 AM[/TD]
[TD="align: right"]10:00 AM[/TD]
[TD="align: right"]11:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]6:00 PM[/TD]
[TD="align: right"]7:00 PM[/TD]
[TD="align: right"]8:00 PM[/TD]
[TD="align: right"]9:00 PM[/TD]
[TD="align: right"]10:00 PM[/TD]
[TD="align: right"]11:00 PM[/TD]
[TD="align: right"]12:00 AM[/TD]
[TD="align: right"]1:00 AM[/TD]
[TD="align: right"]2:00 AM[/TD]
[TD="align: right"]3:00 AM[/TD]
[TD="align: right"]4:00 AM[/TD]
[TD="align: right"]5:00 AM[/TD]
[TD="align: right"]6:00 AM[/TD]
[TD="align: right"]7:00 AM[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]9:00 AM[/TD]
[TD="align: right"]10:00 AM[/TD]
[TD="align: right"]11:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]6:00 PM[/TD]
[TD="align: right"]7:00 PM[/TD]
[TD="align: right"]8:00 PM[/TD]
[TD="align: right"]9:00 PM[/TD]
[TD="align: right"]10:00 PM[/TD]
[TD="align: right"]11:00 PM[/TD]
[TD="align: right"]12:00 AM[/TD]
[TD="align: right"]1:00 AM[/TD]
[TD="align: right"]2:00 AM[/TD]
[TD="align: right"]3:00 AM[/TD]
[TD="align: right"]4:00 AM[/TD]
[TD="align: right"]5:00 AM[/TD]
[TD="align: right"]6:00 AM[/TD]
[TD="align: right"]7:00 AM[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]9:00 AM[/TD]
[TD="align: right"]10:00 AM[/TD]
[TD="align: right"]11:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]6:00 PM[/TD]
[TD="align: right"]7:00 PM[/TD]
[TD="align: right"]8:00 PM[/TD]
[TD="align: right"]9:00 PM[/TD]
[TD="align: right"]10:00 PM[/TD]
[TD="align: right"]11:00 PM[/TD]
[TD="align: right"]12:00 AM[/TD]
[TD="align: right"]1:00 AM[/TD]
[TD="align: right"]2:00 AM[/TD]
[TD="align: right"]3:00 AM[/TD]
[TD="align: right"]4:00 AM[/TD]
[TD="align: right"]5:00 AM[/TD]
[TD="align: right"]6:00 AM[/TD]
[TD="align: right"]7:00 AM[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]9:00 AM[/TD]
[TD="align: right"]10:00 AM[/TD]
[TD="align: right"]11:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]6:00 PM[/TD]
[TD="align: right"]7:00 PM[/TD]
[TD="align: right"]8:00 PM[/TD]
[TD="align: right"]9:00 PM[/TD]
[TD="align: right"]10:00 PM[/TD]
[TD="align: right"]11:00 PM[/TD]
[TD="align: right"]12:00 AM[/TD]
[TD="align: right"]1:00 AM[/TD]
[TD="align: right"]2:00 AM[/TD]
[TD="align: right"]3:00 AM[/TD]
[TD="align: right"]4:00 AM[/TD]
[TD="align: right"]5:00 AM[/TD]
[TD="align: right"]6:00 AM[/TD]
[TD="align: right"]7:00 AM[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]9:00 AM[/TD]
[TD="align: right"]10:00 AM[/TD]
[TD="align: right"]11:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]6:00 PM[/TD]
[TD="align: right"]7:00 PM[/TD]
[TD="align: right"]8:00 PM[/TD]
[TD="align: right"]9:00 PM[/TD]
[TD="align: right"]10:00 PM[/TD]
[TD="align: right"]11:00 PM[/TD]
[TD="align: right"]12:00 AM[/TD]
[TD="align: right"]1:00 AM[/TD]
[TD="align: right"]2:00 AM[/TD]
[TD="align: right"]3:00 AM[/TD]
[TD="align: right"]4:00 AM[/TD]
[TD="align: right"]5:00 AM[/TD]
[TD="align: right"]6:00 AM[/TD]
[TD="align: right"]7:00 AM[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]9:00 AM[/TD]
[TD="align: right"]10:00 AM[/TD]
[TD="align: right"]11:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]6:00 PM[/TD]
[TD="align: right"]7:00 PM[/TD]
[TD="align: right"]8:00 PM[/TD]
[TD="align: right"]9:00 PM[/TD]
[TD="align: right"]10:00 PM[/TD]
[TD="align: right"]11:00 PM[/TD]
[TD="align: right"]12:00 AM[/TD]
[TD="align: right"]1:00 AM[/TD]
[TD="align: right"]2:00 AM[/TD]
[TD="align: right"]3:00 AM[/TD]
[TD="align: right"]4:00 AM[/TD]
[TD="align: right"]5:00 AM[/TD]
[TD="align: right"]6:00 AM[/TD]
[TD="align: right"]7:00 AM[/TD]
[TD="align: right"]8:00 AM[/TD]
[TD="align: right"]9:00 AM[/TD]
[TD="align: right"]10:00 AM[/TD]
[TD="align: right"]11:00 AM[/TD]
[TD="align: right"]12:00 PM[/TD]
[TD="align: right"]1:00 PM[/TD]
[TD="align: right"]2:00 PM[/TD]
[TD="align: right"]3:00 PM[/TD]
[TD="align: right"]4:00 PM[/TD]
[TD="align: right"]5:00 PM[/TD]
[TD="align: right"]6:00 PM[/TD]
[TD="align: right"]7:00 PM[/TD]
[TD="align: right"]8:00 PM[/TD]
[TD="align: right"]9:00 PM[/TD]
[TD="align: right"]10:00 PM[/TD]
[TD="align: right"]11:00 PM[/TD]
[/TR]
</tbody>[/TABLE]

 

[J.Ty.]

1) Most events run about 8 hours, but as little as 1.5 hours and as much as 18 hours.

You have very long backups!

4) If it begins on Sunday at 11:59PM and Duration is 3 hours, how many "Yes" entries do you have in the days of the week? The yes entries correspond to the start times, meaning that the sunday column would have a Yes and unless it begins again on Monday at that time, it would be a No

This puzzles me somewhat. I understand that you have one bacup per row of data, hence "starts again" should be something impossible.

Basically the end result I want is "Is this backup running on this day at this time? If so, put an X in the cell where that backup's row meets that time's column
It may also help, here are the first 2 rows of my sheet with the header names:

Have you noted that the Sunday columns play a double role in your chart?
Indeed they can represent both Sunday at the beginning of the current week and Sunday that follows the current week and you cannot easily tell them apart. If you see "X" ticks for Sunday starting at 12am, you must check the end of the row to see if this is a backup that started on Sunday just after midnight or a continuation of a backup the started late on Saturday. The two possibilities are in fact almost 7 days apart.
Would you mind adding columns for the "second" Sunday after Saturday? It would make you chart easier to interpret, and formulas I'm going to write simpler.


J.Ty.

 

[hejman08]

You have very long backups!


This puzzles me somewhat. I understand that you have one bacup per row of data, hence "starts again" should be something impossible.



Have you noted that the Sunday columns play a double role in your chart?
Indeed they can represent both Sunday at the beginning of the current week and Sunday that follows the current week and you cannot easily tell them apart. If you see "X" ticks for Sunday starting at 12am, you must check the end of the row to see if this is a backup that started on Sunday just after midnight or a continuation of a backup the started late on Saturday. The two possibilities are in fact almost 7 days apart.
Would you mind adding columns for the "second" Sunday after Saturday? It would make you chart easier to interpret, and formulas I'm going to write simpler.


J.Ty.

I work for a major medical corporation. Our backups are ridiculous.

As far as the "one backup per row" what it really is is one backup name per row, and each backup can run as much as 7 days a week or as little as 1. There are around 40 different backups, all named after what they're actually backing up or what server they happen on. That's why the day of the week is there at all- to avoid having 7 instances of "TSM_backupX" or whatever, but rather have all instances of one backup on one row, with x's in each row to show in a full 168 hour week when they run.

I am not in charge of the spreadsheet itself, just volunteered to help with formulas because i'm a bit better with excel than the guy who owns it. I don't think he'd want to add a second Sunday but if there's no other way to work it then perhaps he'll have to suck it up

Thank you again for your time.

 

[J.Ty.]

As far as the "one backup per row" what it really is is one backup name per row, and each backup can run as much as 7 days a week or as little as 1.

Now I do not understand anymore. Let's make it clear again.

In each row, you have one start time and one duration, which is no more than 18 hours. Am I right?
If not, what can I expect there? What is the maximum possible duration?

The answers affect the formulas I'm going to write.

J.Ty.

 

[hejman08]

Now I do not understand anymore. Let's make it clear again.

In each row, you have one start time and one duration, which is no more than 18 hours. Am I right?
If not, what can I expect there? What is the maximum possible duration?

The answers affect the formulas I'm going to write.

J.Ty.

OK so some of the backups run for 1.5 hours and run every night of the week at the same time. Others run for as much as 18 hours and start at the same time but on only 1 or 2 nights a week. for example:
Backup A runs for 1.5 hours, starting Monday, Tuesday and Friday at 11pm
Backup B runs for 18 hours, starting Monday at 11am and Friday at 11am

The start times never differ, but the days of the week do, hence the "Yes" columns for days of the week. The "Yes" columns are basically for "does this backup have a start time that falls on this day of the week?" purpose.

does that help? I know this is complicated.

 

[J.Ty.]

OK, now I hope to understand. It will take me some time, it is not easy.

J.Ty.

 

[J.Ty.]

I have made a solution using a "what-if data table". It is manifested by the formulas {=TABLE(B9,D9)}. You cannot enter them manually, follow instructions below the picture.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1													Sunday	Sunday	Sunday	Sunday	Sunday	Sunday	Sunday	Sunday	Sunday	Sunday	Sunday
2		Start	Duration	End	Sunday	Monday	Tuesay	Wednesday	Thursday	Friday	Saturday		12:00 AM	1:00 AM	2:00 AM	3:00 AM	4:00 AM	5:00 AM	6:00 AM	7:00 AM	8:00 AM	9:00 AM	10:00 AM
3													$M$1	$N$1	$O$1	$P$1	$Q$1	$R$1	$S$1	$T$1	$U$1	$V$1	$W$1
4		11:00 PM	08:00	7:00 AM	Yes	Yes	No	Yes	No	No	Yes	$B$4	X	X	X	X	X	X	X	X
5		12:00 PM	08:00	8:00 PM	No	Yes	No	No	Yes	No	Yes	$B$5
6
7
8
9	Input row	$AT$1	Input column	$B$4	Time unit	01:00
10		Sun	Mon	Tue	Wed	Thu	Fri	Sat
11	Start day	Yes	Yes	No	Yes	No	No	Yes
12	Start hour	23:00:00	round down	23:00:00
13	Duration	08:00
14	End hour	07:00:00	round down	07:00:00
15	Today	Monday
16	Time now	09:00:00	round down	09:00:00
17	Start/End	Today/ today		FALSE
18		Yesterday/today		TRUE
19		Today/ tomorrow		TRUE
20
21	Tick	Today/ today
22		Yesterday/today
23		Today/ tomorrow
24		Total
25
Sheet1

Cell Formulas
Range	Formula
L3	=C24
L4	=CELL("address",B4)
L5	=CELL("address",B5)
M3	=CELL("address",M1)
N3	=CELL("address",N1)
O3	=CELL("address",O1)
P3	=CELL("address",P1)
Q3	=CELL("address",Q1)
R3	=CELL("address",R1)
S3	=CELL("address",S1)
T3	=CELL("address",T1)
U3	=CELL("address",U1)
V3	=CELL("address",V1)
W3	=CELL("address",W1)
D4	=B4+C4
D5	=B5+C5
D12	=FLOOR(B12,$F$9)
D14	=MOD(FLOOR(B14,$F$9),1)
D11	=OFFSET(INDIRECT($D$9),0,2+COLUMNS($B$9:D9))
D16	=$B$16
D17	=AND(INDEX(
B11	=OFFSET(INDIRECT($D$9),0,2+COLUMNS($B$9:B9))
B12	=OFFSET(INDIRECT($D$9),0,0)
B13	=OFFSET(INDIRECT($D$9),0,1)
B14	=OFFSET(INDIRECT($D$9),0,2)
B15	=OFFSET(INDIRECT($B$9),0,0)
B16	=OFFSET(INDIRECT($B$9),1,0)
C11	=OFFSET(INDIRECT($D$9),0,2+COLUMNS($B$9:C9))
E11	=OFFSET(INDIRECT($D$9),0,2+COLUMNS($B$9:E9))
F11	=OFFSET(INDIRECT($D$9),0,2+COLUMNS($B$9:F9))
G11	=OFFSET(INDIRECT($D$9),0,2+COLUMNS($B$9:G9))
H11	=OFFSET(INDIRECT($D$9),0,2+COLUMNS($B$9:H9))

Named Ranges
Name	Refers To	Cells
Today	={"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"}
Yesterday	={"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday"}

In order to get the desired result:

1. The main computing machinery occupies A9:H24. You can move it around, but it must remain on the same worksheet. I will refer to its present position.
2. Formulas in column L and in row 3 are also necessary for the solution to work, you should copy them down and to the right.
3. The actions described below have been already done in the example, but you will have to redo them, when you add more rows with backups.
4. Mark the whole area L3:FX3 (add more rows if necessary).
5. Use Data->What-if analysis-> Data table from the ribbon.
6. Enter D9 as the "column input cell", enter B9 as the "row input cell", click OK
7. enter =C24 in the cell L3.
8. You can hide row 3 and column L if you wish, or set font color to white, etc.

Then Excel automatically processes each of the cells in your chart, fetches the addresses from the corresponding cells in row 3 and column L into B9 and D9. computes all the necessary intermediate values inside A9:H24, with the final result C24, and then transfers that final result into the cell, and starts over for the next cell.

The computing area A9:H24 contains at the moment some example values in B9 and D9, to see how the computation proceeds. It can be used to test what goes wrong in case of incorrect results.

If you wish, you can download my worksheet from this address: http://www.mimuw.edu.pl/~jty/MrExcel/hejman08.xlsx


J.Ty.

 

[J.Ty.]

Sorry, small correction.
I wrote

4. Mark the whole area L3:FX3 (add more rows if necessary).

but I meant L3:FX5.

J.Ty.