1. ibmy

    Unique Value In Range Of Time

    Hi, Example of my 200k row data: ABCDE1TimeA21:00:00.0001.52:00531:30:00.0001.542:00:00.0001.252:01:00.0001.262:45:00.0001.372:50:00.0001.382:55:00.0001.592:59:00.0001.5102:59:08.0001.9112:59:59.0001.9122:59:59.0201.4133:00:00.0001.11422:00:00.0001.4A Column : Time B Column : Value D Column ...
  2. A

    VBA code takes long to excute

    Hi all, I am very new to VBA. I would like my code add a comment box to the active cell based on the text in the active cell. As the user inputs code it should add a comment. However when users move down the sheet and populate the rows it seems that the code takes longer to execute. Any help...
  3. T

    Time is not showing correctly from Excel file

    Hi, I have below Hours:Minutes:Seconds from Excel file 01-02-2021 33:05:51 01-02-2021 14:50:45 01-02-2021 11:19:14 01-02-2021 11:48:32 01-02-2021 07:58:32 01-02-2021 17:08:31 When showing in Power BI in matrix it returns the following: I don't know why, but I can't select sum...
  4. T

    Circle chart showing times of the day

    Hi all, so my problem is this. I want to basically make the attached picture in excel. What this shows is a 24 hour day, divided into various chunks of X hours. This I can kind of do with a multiple-series donut chart (I need multiple concentric wedges), however the bands are WAY too thin and...
  5. 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...
  6. P

    Rounding decimals to the nearest quarter

    I am working on a staff schedule budget. I am trying to calculate based on budget and rate how may hours an employee can work. Here is the catch, while an employee may get a budget of $2889.00 at a rate of $15.00/hr which would give them 192.6 hours. However, since our timesheet system only...
  7. O


    Hi, I have a schedule of employees, that works in shifts. But i want to count how many people are working between 07:00 and 12:00 and between 12:00 and 16:00 and between 16:00 and 20:00 How can i do that, the begin time is in a cell and the end time is in the cell next to it. see down...
  8. N

    Conditional Formatting if difference in time is equal to certain value

    I'm struggling to come up with a formula that will look at the start time and subtract it from the end time (if the dates are the same) and if the difference is 1 minute or 30 minutes, color it green. I've tried googling this and have not come up with anything that works at all. I'm hoping that...
  9. Brett Fields

    Help... I need some sleep!

    Every day I produce a spreadsheet (not of my own design) to report on the vaccination of our workforce. It needs to be out by 7:30 a.m. The raw data is available at 10pm the night before in 4 CSV files. I have 1 macro that imports the new info onto a sheet called "Data", and another macro that...
  10. L

    Google Sheets : How can I display duration of time with a "+" or "-" prefix?

    In Google Sheets I have a column that checks another two column's and calculates the difference. I already have it conditionally formatting to be green or red depending on the difference being less than or equal to 0, and greater than 0 respectively, but I specifically want to have it display...
  11. C

    Distance & Time Calculator (Chandoo)

    Hi All, Hoping to get some help, I'm using the distance and time calculator, that is supplied on the chandoo website, I have my API for bing and everything is working 100% when I save things locally, I have it integrated into a excel project calculator for projects. Which basically calculates...
  12. P

    Select range based on time.

    I have data like the image below. I am trying to select a range from Cell B7 to Cell B908, Do Something, Select range B909 to B1807, Do Something, and continue this loop until I get to the last row of data. The cycle runs about 45sec each time, but there are always multiple instances of the time...
  13. R

    How to convert time (AM and PM) to number of hours

    Please see attached image. In the highlighted green area, I need a calculation that would calculate time rounded up to the nearest 0.25. I am having a contractor work for me, so all time is only like this: 12 PM 12:15 PM 12:30 PM 12:45 PM 1:00 PM So I am not doing hours like 2:34 PM or 5:49...
  14. R

    Calculate a price for a item which was rented on different day's

    I Have a item " spoon " Price is €0.07 per day One customer who has ordered 50 spoons on 1.11.20 ( and still has not returned them). On the 7.11.20 he orders another 40 spoons. On the 14.11.20 another 20. On the 21.11.20 he returned 30 spoons ( I would charge him 21 days at € 0.07) On the...
  15. S

    Timesheet Calculation

    How can I complete this timesheet? I need Total Time worked (End Time less Start Time less Break) and Total $ Due. I've tried it with the Break as minutes or hours or x24, formatted as h:mm or 0.00. I just can't get there. Can you help? Date Start Time End Time Break (Minutes) Break (Hour)...
  16. J

    Macro to Update Links at a specific time of day

    Hello, I have two spreadsheet, both on the same workbook, which I have to update links to every day before our morning meeting. I've added an "Edit Links" shortcut to my "Quick Access Toolbar" so I can easily update the links but sometimes I become busy and forget to update them which causes...
  17. R

    Convert Excel DateTime Value from Double to mm/dd/yyyy hh:mm:ss.000

    Hello Excel Gurus! How can I format an Excel TimeDate from a double to mm/dd/yyyy hh:mm:ss.000 Thank you! Please call this sub to demonstrate my question: Public Sub TimeWithMS() Dim Timestamp As Variant Timestamp = Evaluate("Now()") Debug.Print Tab(0); "Timestamp:"; Tab(15); Timestamp...
  18. C

    Calculating duration sum hh:mm:ss

    I am trying to get a sum of time spent on the phone for agents. The format the duration is in is hh:mm:ss when I format the column and do =C2+C3 I get the correct output. When I do =SUM(C2:C55) the sum is 00:00:00. I've tried a few different ways to format and calculate the sum without luck...
  19. D

    Convert to minute elapsed from specific field

    Hello, i got a list of tickets with the dedicated time for each, i want to convert it in excel so i can sum the time consumed per week, but the time comsumption needs to be converted i wanted to make with if functions but maybe there is a more easier way, i identified 3 variants: -1h -1h 30m...
  20. R

    Formula to Measure Time into Percentage Scoring

    Hi everyone! I hope you all have a pleasant day. I do not know if this is possible in Excel, but I would like to ask. We have a KPI for report submission deadline. The rule is, maximum time for submission is at 00.00 in the same day of working day. This would score a 100%. After exceeding that...

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