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.

Subject	Start Time	Start Date	End Time	End Date
			=IF(A2="Travel",=D2+TIME(0,30,0),=D2)<strike></strike>

<tbody>
</tbody>

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]

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

<tbody>
</tbody>

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

Subject

Travel +30min

Admin +60min

YOS +60min

TSN +75min

VHA +15min

Lunch +60min

VMOB +60min

TBC +60min Training +60min

I hope this makes sense Thanks,

<tbody>
</tbody>

 

[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.