Making a function to output a String name based on a reference of a cell with a time in it

Hi,

I have a long nested if statement that I feel like would be better as a function. At my company, employees' shifts are from 6AM to 6PM and the next shift is from 6PM to 6AM. I want to create a function where the input is a date and time, and the output is the name of the supervisor of that shift at that time. I'm not sure where to start. I looked at using cases but not sure how to reference the input date and time cell.

Thank you

how is this data stored? or did you just hard code it into your if statement? If it was me making this, i would make another sheet and create a small database with the shift times and supervisors names. this way you can make a vlookup formula to lookup the time you specified in cell a and return the supervisor in cell b

Please Login or Register  to view this content.

if you really wanted this done in vba i would need to know what the format you want is for the input. like MM/DD/YYYY HH:MM:SS.

Give me a few minutes and I'll attach a file and you can see what i've done

ShiftFunction.xlsm

So in that file you should be able to see the if statement I have, but in my larger sheet I'm going to need to output who's on shift based on time from another cell. If you have questions let me know, I know it might be somewhat confusing

Look at this and see what you think

Hmm, that helps a little, but I'm looking more for something that can recognize my time input, whether it be 1:33 AM or 3:45 PM and know which person is on shift

Here you go try this

oops,,,ok paste this into the cell where the name pops up (it might say false if you type in lets say 12:55 AM or so

Please Login or Register  to view this content.

alrighty I think I can work with that, thanks for your help!