Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If time falls within time..
Hello,
I am using this formula (with thanks to Bob Phillips) to see if a block of time falls with in a schedule: '=MIN($W$1+TIME(0,30,0),$J4)-MAX($W$1,$I4) W1 = 06:00 J4 = 14:30 I4 = 06:00 So I am really testing the half hour period of 6:00 and 06:30 to see if the employee with the shift 06:00 to 14:30 is scheduled to work, which of course he is. I have two problems with this: The cells that have the formula are formatted as time (13:30) and in my code I have something that refers to "if the cell value is equal to "0:30" then do something". In some cases, it is missing cells that it shouldn't. The reason I formatted the cells to time in the first place is because some cells were off fractionally, so I couldn't just say "if cell is = to 0.02083" So - my first question is, what would be the best way to wrap my formula is something (round, roundup??) that will force the result to the appropriate 30 minutes. The second question is, how to I adapt the formula to account for the fact that the shift end time in J4 may cross midnight. Also, the time in W1 may cross midnight. I understand the concepts, but I just can't get the formula right. Any help would be very much appreciated!! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to find Stop Time from Start Time and Total Minutes | Excel Worksheet Functions | |||
how to calculate time start & time finish in quarter hour | Excel Discussion (Misc queries) | |||
template or formula for start time -finish time -total hours ple | New Users to Excel | |||
Calculating days & time left from start date/time to end date/time | Excel Worksheet Functions | |||
Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions |