midnight

  1. K

    Splitting activity duration by hour intervals

    Hi, i hope someone can help me. I try something similar as already described here: Splitting activity duration by half an hour intervals I too have a long list of activities and their start and end times. What I need is to split the duration of each activity into hourly intervals. At "Normal"...
  2. J

    Car study that goes before and after midnight

    Hi All, I'm running a study where I measure car speeds passing a speed detection device. The device records speeds and spits out one excel spreadsheet per date. I've put together all the spreadsheets using PowerQuery in Excel which has worked nicely. The problems start to arise with the fact...
  3. A

    Working Comparisions With Midnight

    I have an activity start time variable, lrtime equaling 0.7708333333 (7:00 PM) I need to compare it to ensure that lrtime falls within the employee's shift. ie lrtime <= sel_crew1_end. The value for sel_crew1_end = 0 (midnight) sel_crew1_end = WorksheetFunction.VLookup(Me.cb_r1_crew & "1"...
  4. R

    Calculate Elapsed Time in Minutes (results may be negative, or past midnight)

    Hello, I need to calculate how many minutes early/late a train arrived. I am comparing the scheduled train arrival, versus the actual train arrival. I have tried many formulas, but can't find a formula that works for all five scenarios I've encountered in my data below. Please note I must use...
  5. K

    Time Issue at Midnight

    I have an issue with a COUNTIF formula once a schedule goes to midnight and after. In column A I have four times; 21:00, 22:00, 23:00 and Midnight. The first formula only looks at A2-A4; omitting midnight: Count, If A2 through A4 is greater than or equal to 9:00 PM, the result is 3; this is...
  6. M

    Perform function by groups

    Hi ALL... How can I do a function (RANK) within groups separated by blank rows? <colgroup><col span="5"><col><col span="17"></colgroup><tbody> Date Post Track Racenum Prg Horse ML cFT cF4 cHF cRtg PSR CPw Points RANK 3/23/19 10:30 AQU 1 1 Blue Pigeon 15 94 24 90 67 78 72 39.67...
  7. D

    Subtracting Times when passing midnight

    =IF(OR(ISBLANK(P2),ISBLANK(O2)),"",P2-O2) is the current formula I am using to subtract times and leave the cell blank if target cells are blank. This works perfectly 99% of the time, the issue I am running into is when a call runs through midnight. I need to be able to add (P2>O2). Ex...
  8. T

    HELP PLEASE Counting hours of the day for any given hour of the day

    Hi All, I am trying to calculate a count of the hours of the day patients are in our facility, So if a person is admitted at say: 04/01/2019 3:45:00 PM and discharged at 04/01/2019 5:30:00 PM, then that patient would be counted at the 3rd, 4th and 5th hour. I have created a spreadsheet that...
  9. T

    HELP PLEASE Counting hours of the day for any given hour

    Hi All, I am trying to calculate a count of the hours of the day patients are in our facility, So if a person is admitted at say: 04/01/2019 3:45:00 PM and discharged at 04/01/2019 5:30:00 PM, then that patient would be counted at the 3rd, 4th and 5th hour. I have created a spreadsheet that...
  10. R

    headcount by hour spanning midnight

    Hello, I am looking for a formula/method to calculate the headcount by hour of day. I have employees whose shift crosses midnight hours. e.g. employee Start End A 06:00 14:30 B 14:00 22:30 C 22:00 06:30 Now I would...
  11. F

    Attendance After Midnight

    Hi All, I am trying to make a Conditional Formation rule to highlight employees who left early then the schedule time out. But due to Midnight, I am stuck. The scheduled timing is from 04:00 PM to 12:00 AM (Midnight). Following is attendance of an employee... <tbody> A B 1 Time In Time Out...
  12. M

    Excel Using Greater than less than with time

    Hello - I have a situation which requires me to isolate three separate time periods and return different results. If the time is >= Midnight (oo:oo:oo), but less than 8:00 AM - I need to return the result "Midnight" If the time is >= 8:00 AM, but less than 16:00 (4:00 PM) - I need to return...
  13. N

    Run a macro on last day of each month at midnight

    Hi I have a file that has a Mracro (Macro2) to refresh the data and a pivot table. I want to be able to automatically run this macro on the last day of each month at midnight. The file would not be open at that time this would need to be run, so will need to open the file on the last day of...
  14. D

    Ontime Event Going haywire at midnight

    Hello! I have a excel project that starts an ontime event on workbook open. This thing works fantastic! However, when midnight rolls around it crashes. After doing some research I have surmised that the problem is when the date changes, the time is now After the initial time set. I tried to...
  15. D

    subtracting time when answer is before midnight

    I need to subtract 1 hour and 51 minutes from 1:00 am to get the answer of 11:09pm
  16. T

    Formulas with times past midnight big help required

    OK, I've struggled with this for most of today so any help would be gratly appreciated.... I have data for each HH of the week arranged as follows: Sun 00:30 Sun 01:00 sun 01:30 etc to sat Below this I have Seperate day opening / closing hours: Monday 11am - 11pm Tuesday 11am - 2am etc...
  17. D

    Creating a (Gantt) sleep chart

    OK, after browsing the web for a few days, I can’t figure out how to create this specific Gantt sleep chart. It has with 6 daily repeated tasks (in this case sleep sessions), with data spread past midnight, from 18h00 until 06h00. The closest online instruction I found, was this one, but it...
  18. J

    Formula for minutes early and late before and after midnight.

    Hi, I have been working on a spreadsheet which displays the minutes a vehicle if early or late on site. My problem however is that some of the arrivals happen after midnight (00:00); and the formulas i have been using work for wither before or after, not both. <tbody> Scheduled On Site On...
  19. andrewb90

    Errors after midnight in formula calculation

    Hello All, I am having an issue with accurately counting hours worked. Now, my formulas all work fine until the ending point of a shift reaches the AM. For Example: 6PM - 1AM would be a 7 hour shift, but the result is negative 17 hours. Now I am using two different set of formulas that are...
  20. G

    Validating times that span midnight

    I am working on a spreadsheet to track machine downtime. The user enters the start time and end time of any machine downtime. The time entered must occur on or after the lot start time or shift start time (whichever occurs first) and on or before the lot end time or shift end time, (whichever...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top