Formula needed for Time Ranges

[rpjuanm]

I need help creating a formula that will display an employees name only if the start and stop time of their meeting falls within a specific range and NOT display the name if it does not fall within that time range. Example. Time range is from 11:37 to 14:57 and the meeting is from 14:30 to 15:00.

Help!

 

[Peter_SSs]

You haven't told us anything about your existing layout or where/how you want the results presented. however, see if this helps get you going. Formula in E3 is copied down.

Excel Workbook
	A	B	C	D	E	F	G
1	Name	Start	Stop		Meeting	Start	Stop
2						14:30	15:00
3	Tom	8:30	13:30
4	Jen	11:00	15:30		Jen
5	Ann	14:30	18:00		Ann
6	Jim	11:00	14:45
7
Meeting

 

[rpjuanm]

I have the exact same format as what you created, but when i copy the formula over to my sheet, it does not display the names as yours does. One thing i should mention is that I have formulas that calculate the meeting start and stop, but they are in the same format (custom "H:MM"). Any thoughts as to why it is not working when i move it over to my sheet?

 

[Peter_SSs]

I have the exact same format as what you created, but when i copy the formula over to my sheet, it does not display the names as yours does. One thing i should mention is that I have formulas that calculate the meeting start and stop, but they are in the same format (custom "H:MM"). Any thoughts as to why it is not working when i move it over to my sheet?

Does it display no names at all, some names but they are the wrong ones? Tell us as much as you can.

My guess is that at least part of the problem is that you have a rounding error. If you are calculating start/stop times with a formula, that formula possibly results in a time that is not exactly any number of hours and minutes but results in hours, minutes and seconds, with just hours and minutes displayed.

However, that should only be a problem when the meeting start/stop time is almost identical to the persion's start/stop time.

Can you give us the formulas and possibly some sample data?

 

[rpjuanm]

how can i post a screen shot?

 

[Peter_SSs]

how can i post a screen shot?

Excel jeanie

 

[rpjuanm]

Any way to remove the decimal rounding errors? perhaps timevalue()?

 

[Peter_SSs]

Try:

=ROUND(your_current_formula*24*60,0)/24/60
which really becomes
=ROUND(your_current_formula*1440,0)/1440