Adding Time with IF Function

[Syringa]

Hi Everyone,

I am trying to create a calendar that will prefill specific areas for me.

e.g. If "Subject" column contains the word "Travel" and "Start Time" is 14:30 then "End Time" should automatically add 30 mins and become 15:00.

This is my first attempt at using IF functions and I am pretty novice when it comes to excel so I apologise if I am totally off base with this.

The formula I am attempting to use is =IF(A2="Travel",=D2+TIME(0,30,0),=D2) - However as soon as I type it in the brackets around the time function go red.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Subject
[/TD]
[TD]Start Time
[/TD]
[TD]Start Date
[/TD]
[TD]End Time
[/TD]
[TD]End Date
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=IF(A2="Travel",=D2+TIME(0,30,0),=D2)<strike></strike>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I have a feeling I maybe entering the formula in the wrong column as well but I have tried in the first column as well and get a circular reference error.

ANy help would be sincerely appreciated.

 

[kieran]

Try

=IF(A2="Travel",D2+TIME(0,30,0),D2)

an '=' starts the formula, however it is not required (creates an error actually) if it is used in the part of the formula that is ,=D2+TIME(0,30,0),=D2)

 

[Syringa]

Try

=IF(A2="Travel",D2+TIME(0,30,0),D2)

an '=' starts the formula, however it is not required (creates an error actually) if it is used in the part of the formula that is ,=D2+TIME(0,30,0),=D2)

Thanks Kieran, that worked perfectly. Although I now think I am being a bit too ambitious with what I want this spreadsheet to do. I was hoping to have multiple IF functions apply to each cell (i.e if Cell A2 contains the word Travel, add 30 mins, if the same cell contains the word "Store A", add an hour etc with up to 7 options). I'm thinking this is now veering into Macro Territory which I am very far away from.

 

[cbatrody]

Hi,

It can be done with IF or INDEX MATCH functions. What are the 7 options and the respective outcomes?

 

[Syringa]

[TABLE="width: 500"]
<tbody>[TR]
[TD]<strike></strike>

Subject[/TD]
[TD]Location<strike></strike>[/TD]
[TD]Start Time<strike></strike>[/TD]
[TD]Start Date<strike></strike>[/TD]
[TD]End Time<strike></strike>[/TD]
[TD]End Date<strike></strike>[/TD]
[TD]All day Event<strike></strike>[/TD]
[TD]Categories<strike></strike>[/TD]
[TD]Show as<strike></strike>[/TD]
[/TR]
[TR]
[TD]Travel[/TD]
[TD][/TD]
[TD]8:00[/TD]
[TD]1/09/2015[/TD]
[TD]8:30[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]No[/TD]
[TD]Travel[/TD]
[TD]Free[/TD]
[/TR]
[TR]
[TD]Admin<strike></strike>[/TD]
[TD][/TD]
[TD]8:30[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]9:30[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]No[/TD]
[TD]Admin[/TD]
[TD]Free[/TD]
[/TR]
[TR]
[TD]YOS<strike></strike>[/TD]
[TD][/TD]
[TD]9:30[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]10:30[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]No[/TD]
[TD]Store Visit[/TD]
[TD]Tentative[/TD]
[/TR]
[TR]
[TD]TSN<strike></strike>[/TD]
[TD][/TD]
[TD]10:30[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]11:45[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]No[/TD]
[TD]Store Visit<strike></strike>[/TD]
[TD]Tentative<strike></strike>[/TD]
[/TR]
[TR]
[TD]VHA<strike></strike>[/TD]
[TD][/TD]
[TD]11:45[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]12:00[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]No[/TD]
[TD]Compliance[/TD]
[TD]Free[/TD]
[/TR]
[TR]
[TD]Lunch<strike></strike>[/TD]
[TD][/TD]
[TD]12:00[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]13:00[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]No[/TD]
[TD]Lunch[/TD]
[TD]Free[/TD]
[/TR]
[TR]
[TD]VMOB<strike></strike>[/TD]
[TD][/TD]
[TD]13:00[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]14:00[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]No[/TD]
[TD]Store Visit<strike></strike>[/TD]
[TD]Tentative<strike></strike>[/TD]
[/TR]
[TR]
[TD]Travel<strike></strike>[/TD]
[TD][/TD]
[TD]14:00[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]14:30[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]No[/TD]
[TD]Travel[/TD]
[TD]Free[/TD]
[/TR]
[TR]
[TD]YOS<strike></strike>[/TD]
[TD][/TD]
[TD]14:30[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]15:30[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]No[/TD]
[TD]Store Visit<strike></strike>[/TD]
[TD]Tentative<strike></strike>[/TD]
[/TR]
[TR]
[TD]TBC<strike></strike>[/TD]
[TD][/TD]
[TD]15:30[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]16:30[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]No[/TD]
[TD]Store Visit<strike></strike>[/TD]
[TD]Busy[/TD]
[/TR]
[TR]
[TD]Admin<strike></strike>[/TD]
[TD][/TD]
[TD]16:30[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]17:30[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]No[/TD]
[TD]Admin[/TD]
[TD]Free[/TD]
[/TR]
[TR]
[TD]Training Session<strike></strike>[/TD]
[TD][/TD]
[TD]17:30[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]18:30[/TD]
[TD]1/09/2015<strike></strike>[/TD]
[TD]No[/TD]
[TD]Training[/TD]
[TD]Busy[/TD]
[/TR]
</tbody>[/TABLE]

I would like to create a calendar that autofills time depending on the subject, above is an example of a typical scheduled day

So ultimately I would like different subjects to add different time periods.

i.e

[TABLE="width: 108"]
<tbody>[TR]
[TD]Subject[/TD]
[/TR]
[TR]
[TD]Travel +30min[/TD]
[/TR]
[TR]
[TD]Admin +60min[/TD]
[/TR]
[TR]
[TD]YOS +60min[/TD]
[/TR]
[TR]
[TD]TSN +75min[/TD]
[/TR]
[TR]
[TD]VHA +15min[/TD]
[/TR]
[TR]
[TD]Lunch +60min[/TD]
[/TR]
[TR]
[TD]VMOB +60min[/TD]
[/TR]
[TR]
[TD]TBC +60min
Training +60min[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]I hope this makes sense

Thanks,[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[gaz_chops]

Try

=D2+LOOKUP(A2,{"Admin","Lunch","TBC","Training","Travel","TSN","VHA","VMOB","YOS"},{"00:60","00:60","00:60","00:60","00:30","00:75","00:15","00:60","00:60"})

 

[gaz_chops]

Another option, which is better if you are likely to add or edit these values, is to create a small table, like

[TABLE="width: 130"]
<!--StartFragment--> <colgroup><col width="65" span="2" style="width:65pt"> </colgroup><tbody>[TR]
  [TD="width: 65"]Admin[/TD]
  [TD="class: xl63, width: 65, align: right"]01:00[/TD]
 [/TR]
 [TR]
  [TD]Lunch[/TD]
  [TD="class: xl63, align: right"]01:00[/TD]
 [/TR]
 [TR]
  [TD]TBC[/TD]
  [TD="class: xl63, align: right"]01:00[/TD]
 [/TR]
 [TR]
  [TD]Training[/TD]
  [TD="class: xl63, align: right"]01:00[/TD]
 [/TR]
 [TR]
  [TD]Travel[/TD]
  [TD="class: xl63, align: right"]00:30[/TD]
 [/TR]
 [TR]
  [TD]TSN[/TD]
  [TD="class: xl63, align: right"]01:15[/TD]
 [/TR]
 [TR]
  [TD]VHA[/TD]
  [TD="class: xl63, align: right"]00:15[/TD]
 [/TR]
 [TR]
  [TD]VMOB[/TD]
  [TD="class: xl63, align: right"]01:00[/TD]
 [/TR]
 [TR]
  [TD]YOS[/TD]
  [TD="class: xl63, align: right"]01:00[/TD]
 [/TR]
<!--EndFragment--></tbody>[/TABLE]

I put this in K2:L10

Then use

=D2+VLOOKUP(A2,$K$2:$L$10,2,0)

If you add more then simply extend the range in the formula.

 

[Syringa]

That's Amazing, thank you both so much for your help. I have been wrestling with this for weeks. The VLOOKUP has worked exactly how I wanted it to.

Thanks again guys.

 

[gaz_chops]

You're welcome.