Reference,calculate and convert multiple cells with multiple options

[caspertg]

Hey Superstars,

In a tab named "Schedule" i have a 'list' of text options (morning,afternoon,half day etc...).
The source data for this in a sheet called "shifts". This has the shift (morning,afternoon) and then i have 2 additional fields (start and finish time).

In the "hours" tab I have used ='Schedule'... to have everything the same. This also has a start and finish time.

My problem is within this tab.
I need the finish time to automatically populate based on the shift scheduled to work.

My stupid mans explanation would be this:
in the hours tab I would like the following in a workable formula.

"Hours" cell a1 - If schedule d4 is "afternoon" then finish time should auto populate to 21:00.

I know i have been a bit erratic here so please feel free to ask any questions.

tabs: Schedule,Shifts,Hours.
All values need to be in time format as it is for salary payments.


Thanks a mil for any help

 

[MetalBee]

Hey Superstars,

In a tab named "Schedule" i have a 'list' of text options (morning,afternoon,half day etc...).
The source data for this in a sheet called "shifts". This has the shift (morning,afternoon) and then i have 2 additional fields (start and finish time).

In the "hours" tab I have used ='Schedule'... to have everything the same. This also has a start and finish time.

My problem is within this tab.
I need the finish time to automatically populate based on the shift scheduled to work.

My stupid mans explanation would be this:
in the hours tab I would like the following in a workable formula.

"Hours" cell a1 - If schedule d4 is "afternoon" then finish time should auto populate to 21:00.

I know i have been a bit erratic here so please feel free to ask any questions.

tabs: Schedule,Shifts,Hours.
All values need to be in time format as it is for salary payments.


Thanks a mil for any help

by sounds of it you need to use an IF function.

eg

=if(d4="afternoon","21:00","")

this will show in the cell 21:00 if the value in d4="afternoon"

you can string a if functions together:

=if(d4="afternoon","21:00",if(d4="morning","16:00",""))

the "" if required at the end for a false statement. this will return a blank cell. if you want a false statement to return another value finish your formula as such:

..."16:00","false"))

you can put whatever you like between the inverted commas in any part of the formula, to search for values or return values as desired.

 

[MetalBee]

if you have a longer list of values you may want to look at vlookups? if the above does not help, post some example and im sure i can help.

 

[caspertg]

hey thanks for the help.

in your string the end has 2 ).
in my string i have 4.
Am i correct in assuming that this means for each level of formula i need a closing bracket?
Here's my string:
=IF(A3="Weekend Full","19:30",IF(A3="Weekend Half","15:30",IF(A3="Morning","13:30",IF(A3="Afternoon","21:00"))))