1. M

    OT Hours Calculation as per conditions.

    Hi, A formula help is needed to return a result for columns E and F based on start and finish times specified in columns C and D. In actual working days, the OT01 hours should be calculated below the finish time at 9 PM. The OT02 is calculated after 9 PM and the total number of hours spent on...
  2. JEH105

    IF NOW is between specific timeframes not counting both HOUR and MINUTE - HELP PLS :)

    Can anyone assist with figuring out a way to write out this function to where it takes both hour and minutes into consideration when comparing the NOW time? Details: This is the formula I have under John Smith 1 row...
  3. J

    Display start time and end time overlapping

    Hi Everybody, I'm new on the board. I have a problem with ovelapping time and impossible to find a good solution. I need to display the start hour and end hour of overlapping. Bellow a sample: The "What I need" column, is what I need ;) The row 2 is the reference time for overlapping. The...
  4. L

    Rounding time up to the nearest quarter hour

    I am tracking time for short projects and having trouble with rounding to the nearest quarter hour. I am not sure why, but sometimes projects that are exactly 15 minutes round up to the next quarter hour and sometimes it remains at 15. I would like projects of 15 minutes or less to bill at 15...
  5. 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"...
  6. 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...
  7. M

    Calculate the difference in hours in a range write in a multiplelines

    I have a situation like this: In one cell, I split the time ranges over several lines, for example 07: 15-10: 27 10: 35-11: 00 11: 15-12: 27 13: 15-13: 27 14: 15-18: 27 19: 15-20: 27 21: 15-23: 39 I know it would be more logical to have an hourly range divided over several lines, unfortunately I...
  8. I

    How would I convert time values to words?

    Hello. I have a workbook I created to track break times at my work. I have a column of times in a workbook in the custom format [h]:mm:ss. Example 0:06:00 for six minutes. This column tells us how long the person went over the required break time. I would like to, in another column display this...
  9. T

    Greater than / less than when in a table.

    So, I'm pretty much a noob at Excel and this will probably be one of those kinds of questions, but here goes. I have a table with the first two columns being 1 hour increment markers (staggered +1hr), labeled T1 and T2. the next column will be a set of COUNTIFS functions asking if a set of...
  10. A

    Hours.Minutes to Hours.Tenths

    I have a spreadsheet where time is reported in hours.minutes and I need to report in Hour.tenths of an hour to one decimal, rounded up. Not sure what the formula should look like.
  11. S

    Trying to build a running total by hour

    Hi I'm trying to build a formula that has the running total by hour. I use CUBE members Here is the hourly cube: =IFERROR(CUBEMEMBER("ThisWorkbookDataModel","[TIME].[TIME].&[1899-12-30T10:00:00]"),"") This is the sales formula pointing to the hour above...
  12. S

    Running total by hour

    Hi, i'm trying to build a running total by hour. I have a power pivot CUBE formula for the time: =IFERROR(CUBEMEMBER("ThisWorkbookDataModel","[TIME].[TIME].&[1899-12-30T10:00:00]"),"") And what i need is to see the total up to this hour. I'm pulling in last years values. If the hour is after...
  13. G

    Checking Time blocks

    Hello, im trying to create a check for a bunch of time blocks for staff ID Staff Start End Total Time 1111 8:30 9:30 0:30 "Under 1 hour" 1111 10:00 10:30 0:30 "OK" 1111 10:30 11:00 0:30 "OK" 1111 12:00...
  14. D

    Running Count by Hour of the Day

    Hello, I have a problem that I cannot solve... I have a dataset that includes an "Arrival Time", an "Exit Time", and a calculated "Hours in Department". I am trying to calculate to number of visits that are currently in the department at each hour of the day. Example: I have 3 visits that...
  15. E

    possible to have WORKDAY FUNCTION holiday range to include time stamp?

    Hi. = Workday(A1,1,[holidays]) For the "holidays" I have no problem with my list: (1/1/19, 1/21/19, 2/18/19, etc.). However, I am making a stock market calendar with different time increments. Sometimes the market closes an hour earlier to observe the upcoming holiday. Thus instead of...
  16. N

    Convert duration in text to HH:MM:SS format in excel

    I have an extract of data where the duration for an activity is listed as ## hour ## Minutes ## Seconds ex. 1 hour 22 minutes 29 seconds. Is there a way to convert this a time format so I can perform date math between two activities? Not all entries have hour, minutes and seconds. There are...
  17. E

    Arrange data from a 24hr cycle to by day rather than by hour

    Hello, My power company exports my energy use by hour. I want to arrange years worth of data by day with all the hours of a day to the right of that day's row. That way I can compare energy use by hour and even by day of the week. What would be the best way to go about organizing this...
  18. L

    I have a world time that have a counter

    i have question time that counter for code Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim hour As Boolean hour = Not (hour) Do While hour = True DoEvents Range("A3") = TimeValue(Now) Loop End Sub how i get to start when you open the file witout click anywhere in a cell TY Thomas
  19. J

    DAX: Grand total correct, calculated fields incorrect

    The goal: In excel, determine hour by hour how much I am paying in labor versus how much we make in sales during that hour. Tables: Timesheets: Simple employee name, start time, stop time, date Timesheets EDIT (separate table): unpivoted start and stop time. Now I have a column called shift...
  20. T

    Change from Hour to hour and minute

    Hello Im new to excel and wondering would it be possible to change the hour formula to hour and minute? example first column which has hour {0;7;22} can it be changed to {0:30;7:30:22:30} as in the energy market it always starts at 7:30 not 7 for example, LOOKUP(HOUR(B2),{0;7;22},{"Off...

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