1. 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...
  2. 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...
  3. 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...
  4. 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...
  5. 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.
  6. 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...
  7. 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...
  8. 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...
  9. 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...
  10. 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...
  11. 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...
  12. 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...
  13. 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
  14. 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...
  15. 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...
  16. C

    Slicer for 12 hour period

    Hi Is it possible to make slicer for 12 hour period from 6am to 6pm and 6pm to 6am? If so how would I do this Any help would be greatly appreciated Thank you Craig
  17. H

    Pivot: average of counts for multiple criteria

    I have a fairly simple pivot table which shows call center data in a very useful and intuitive way for managers. Below is the sum of the number of calls for a given hour and day of the week in March. What I need to show is the average of the calls received for a given hour & weekday (word...
  18. C

    RE: Displaying a Msgbox 1 hour after the workbook opens

    RE: Displaying a Msgbox 1 hour after the workbook opens This code is in the Workbook Open() event: Private Sub Workbook_Open() If Day(Now) = 10 Then MsgBox "Good morning" else End if This works great but displays the MsgBox as soon as soon the workbook is opened. How do I get the MsgBox...
  19. C

    Summing a Value within a Specific Time Frame

    I have a file that contains values gathered at 15-minute intervals for an entire day for hundreds of meters. I need to sum these values by each hour for each meter. Most of the entries contain values for each 15-minute period in the each hour (0:15, 0:30, 0:45, 1:00), but not all show all four...
  20. M

    List of time data using start and end times and presenting duration as an hourly table

    Hi all, I have a list of activities that include the start and end time - I need to sum the duration of these activities into a table with hourly intervals. It would be easy enough if all the activity was only an hour in duration :laugh:, however there are activities that are an hour and 30...

Watch MrExcel Video

This Week's Hot Topics

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