Need to work out what I thought was simple enough but basically need to return if an incident occurred after midnight. Also a formula to calculate the difference between the time of the incident compared to a selected threshold time eg if the threshold time was 11:30 pm then how long after that was the incident? I've included required output to help explain the situation. The problem is that the data comes in with the time as text and doesn't allow any meaningful calculations. The threshold time is to be set dynamically.
Book1 | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | Staff | Time occurred | After midnight | How long after Threshold | Threshold | ||||
2 | Friday | 12:18 | |||||||
3 | Thursday | 10:50 | |||||||
4 | Wednesday | 10:47 | |||||||
5 | Tuesday | 11:31 | |||||||
6 | Monday | 10:32 | |||||||
7 | Sunday | 11:48 | |||||||
8 | Saturday | 1:41 | |||||||
9 | |||||||||
10 | Required ouptut | ||||||||
11 | Staff | Time occurred | After midnight | How long after Threshold | Threshold | 11:45 | |||
12 | Friday | 12:18 | Yes | 33 | |||||
13 | Thursday | 10:50 | |||||||
14 | Wednesday | 10:47 | |||||||
15 | Tuesday | 11:31 | |||||||
16 | Monday | 10:32 | |||||||
17 | Sunday | 11:48 | 3 | ||||||
18 | Saturday | 1:41 | Yes | 116 | |||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D12 | D12 | =33 |