Using Times to make an IF statement

H

hejman08

New Member
Joined
Jun 7, 2013
Messages
7
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
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.
 
Last edited:
Upvote 0
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:
SundayMondayTuesdayWednesdayThursdayFridaySaturday
NAME of backupStart Time DurationEnd TimeSundayMondayTuesdayWednesdayThursdayFridaySaturday12:00 AM1:00 AM2:00 AM3:00 AM4:00 AM5:00 AM6:00 AM7:00 AM8:00 AM9:00 AM10:00 AM11:00 AM12:00 PM1:00 PM2:00 PM3:00 PM4:00 PM5:00 PM6:00 PM7:00 PM8:00 PM9:00 PM10:00 PM11:00 PM12:00 AM1:00 AM2:00 AM3:00 AM4:00 AM5:00 AM6:00 AM7:00 AM8:00 AM9:00 AM10:00 AM11:00 AM12:00 PM1:00 PM2:00 PM3:00 PM4:00 PM5:00 PM6:00 PM7:00 PM8:00 PM9:00 PM10:00 PM11:00 PM12:00 AM1:00 AM2:00 AM3:00 AM4:00 AM5:00 AM6:00 AM7:00 AM8:00 AM9:00 AM10:00 AM11:00 AM12:00 PM1:00 PM2:00 PM3:00 PM4:00 PM5:00 PM6:00 PM7:00 PM8:00 PM9:00 PM10:00 PM11:00 PM12:00 AM1:00 AM2:00 AM3:00 AM4:00 AM5:00 AM6:00 AM7:00 AM8:00 AM9:00 AM10:00 AM11:00 AM12:00 PM1:00 PM2:00 PM3:00 PM4:00 PM5:00 PM6:00 PM7:00 PM8:00 PM9:00 PM10:00 PM11:00 PM12:00 AM1:00 AM2:00 AM3:00 AM4:00 AM5:00 AM6:00 AM7:00 AM8:00 AM9:00 AM10:00 AM11:00 AM12:00 PM1:00 PM2:00 PM3:00 PM4:00 PM5:00 PM6:00 PM7:00 PM8:00 PM9:00 PM10:00 PM11:00 PM12:00 AM1:00 AM2:00 AM3:00 AM4:00 AM5:00 AM6:00 AM7:00 AM8:00 AM9:00 AM10:00 AM11:00 AM12:00 PM1:00 PM2:00 PM3:00 PM4:00 PM5:00 PM6:00 PM7:00 PM8:00 PM9:00 PM10:00 PM11:00 PM12:00 AM1:00 AM2:00 AM3:00 AM4:00 AM5:00 AM6:00 AM7:00 AM8:00 AM9:00 AM10:00 AM11:00 AM12:00 PM1:00 PM2:00 PM3:00 PM4:00 PM5:00 PM6:00 PM7:00 PM8:00 PM9:00 PM10:00 PM11:00 PM

<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>
</tbody>
 
Upvote 0
hejman08 said:
1) Most events run about 8 hours, but as little as 1.5 hours and as much as 18 hours.
Click to expand...
You have very long backups!

hejman08 said:
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
Click to expand...
This puzzles me somewhat. I understand that you have one bacup per row of data, hence "starts again" should be something impossible.


hejman08 said:
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:
Click to expand...
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.
 
Upvote 0
J.Ty. said:
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.
Click to expand...

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.
 
Upvote 0
hejman08 said:
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.
Click to expand...

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.
 
Upvote 0
J.Ty. said:
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.
Click to expand...

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.
 
Upvote 0
OK, now I hope to understand. It will take me some time, it is not easy.

J.Ty.
 
Upvote 0
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
ABCDEFGHIJKLMNOPQRSTUVW
1SundaySundaySundaySundaySundaySundaySundaySundaySundaySundaySunday
2StartDurationEndSundayMondayTuesayWednesdayThursdayFridaySaturday12:00 AM1:00 AM2:00 AM3:00 AM4:00 AM5:00 AM6:00 AM7:00 AM8:00 AM9:00 AM10: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
411:00 PM08:007:00 AMYesYesNoYesNoNoYes$B$4XXXXXXXX
512:00 PM08:008:00 PMNoYesNoNoYesNoYes$B$5
6
7
8
9Input row$AT$1Input column$B$4Time unit01:00
10SunMonTueWedThuFriSat
11Start dayYesYesNoYesNoNoYes
12Start hour23:00:00round down23:00:00
13Duration08:00
14End hour07:00:00round down07:00:00
15TodayMonday
16Time now09:00:00round down09:00:00
17Start/EndToday/ todayFALSE
18Yesterday/todayTRUE
19Today/ tomorrowTRUE
20
21TickToday/ today
22Yesterday/today
23Today/ tomorrow
24Total
25
Sheet1
Cell Formulas
RangeFormula
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
NameRefers ToCells
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.
 
Upvote 0
Sorry, small correction.
I wrote
4. Mark the whole area L3:FX3 (add more rows if necessary).
Click to expand...
but I meant L3:FX5.

J.Ty.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Nested IF statements to analyze time data
Replies
6
Views
180
RobOrBob
R
F
  • Locked
  • Question
Formula to count days between 2 dates/times excluding weekends but including only partial days depending on when the date/time falls (see description)
Replies
2
Views
635
Peter_SSs
Peter_SSs
T
  • Solved
Finding the working hours between two dates and times
2
Replies
12
Views
396
kvsrinivasamurthy
K
F
  • Solved
Take date/time and calculate the time in between excluding weekends and including partial days
2
Replies
15
Views
982
freeb1893
F
J
  • Solved
VBA or Excel – Time sheet with RT / OT based on the day of the week.
Replies
5
Views
302
awoohaw
awoohaw

Forum statistics

Threads
1,216,069
Messages
6,128,599
Members
449,460
Latest member
jgharbawi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top