1. G

    Conditional format based on Cell header being between timeframes

    Hi, Been a while since I've been stumped, and I'm sure its easier than I'm making it out to be I want to highlight the cells C2:H5 based on if the row header is within the time range A:B relative to the row of coarse Any help would be great, i know i ts a conditional formation by...
  2. J

    Need help calculating Day/Night Shift Hours.

    I've looked around and have tried other solutions I've seen posted for similar questions but none have worked completely. This is a sample of the data. Formula for day hours currently is: =MAX(,MIN(D2,IF(B2<A2,1,)+B2)-MAX(A2,C2)) Night hours: =E2-F2 It appears to work in all day shift start...
  3. V

    Add hour in time with condition

    Excel 2019 In cell A1 we have timestamp 23/06/2018 12:02:18 No in cell B2 we want to add four hours in but if timestamp is 23/06/2018 14:00:00 and office open and close time is 11:00 am to 5:00 pm then it will add four hours, according to office working hours 24/06/2018 13:00:00 and if...
  4. F

    Formula for Time

    Greetings, Can someone please help me with a formula in excel to export only the hour, for example column A says 5:31:02 AM, I would like column B to say 5:00:00 AM? Thanks in advance
  5. N

    Excel Macro how do I filter time using VBA to copy/paste to different sheet

    I'm trying to use VBA to automate a process I perform multiple times a day. I've figured out a little bit but I've got 2 different columns containing time. The estimated time is sometimes populated so I want to be able to filter on 2 conditions: either ET (Estimated Time) is populated and...
  6. WarrenCarr

    Why is the Time not adding correcting?

    I am trying to use a basic formula D2+E2, but it wont add the time right. It should say 2:07 for time stop. (2:07+00:00) but it says 7 minutes. Any ideas why?
  7. L

    Cell with current time without the date

    I am trying to create an IF statement that reads "Valid" if the current time is between the hours of 10AM and 3PM. To do this my idea was to have an isolated cell which contains a formula which displays exclusively the current time that can then be referenced in said IF statement. However, I...
  8. S

    Percentage of time worked

    Hi! This is my first post here and I am sorry if the question has already been asked and answered. Couldn't find anything for this. Anyways! I can't figure out how to get the % total of hours worked. The formula for total is: =SUM(L85:R85) ,for example. How do I make that into percent if a the...
  9. C

    Random select time from game clock

    For quality analysis I want to select a random 20 minutes from a 80-minute rugby game. How do I customise the '=TEXT(RAND(), "MM:SS")' so that its giving me a starting number between 0.00 and 80.00?
  10. J

    Formula to SUM "On Hold" time overlaps (minus non-work time, weekends & public holidays)

    Hi. I have a spreadsheet with two sheets, Sheet1 holds my main data set which is an audit trail of incident assignments each row representing an assignment to a support group. The key columns are Incident Number (column A), Assignment Start Date/Time (column B) and Assignment End Date/Time...
  11. Z

    Excel Copying and pasting Time error

    Okay, So I have a document I have been building for a schedule. I have a control sheet that has all the control data, then Base Data which has the same data plus different times for different time zones. Then I have other sheets that have the same data but are related to their time zone. It...
  12. J

    Time formatting

    I have a spreadsheet that is generated nightly by our ERP system and I think the timestamps are being entered in 2 different way, or something? When I try to pivot the table, I can't group the time nor sort it by time. I've tried every different time format but nothing seems to work. I think...
  13. W

    How to remove repeating time intervals?

    17:58:282020-08-02LocationDim LevelCHANGE17:57:092020-08-02xOffOn to Off17:06:452020-08-02xOnOn16:45:362020-08-02xOffOn to Off08:15:032020-08-02xOnOn02:43:522020-08-02xOffOff02:33:502020-08-02xOffOn to...
  14. N

    Countifs -> Dates in 2 cells are equal & before time

    Hi everyone, I'm trying to count the amount of times 2 different cells have the same date and also the times in a 3rd cell need to match and the 4th cell need to be before/after a certain time. In my formula I've only managed to put in the times, so if the date is incorrect it will be counted...
  15. Sufiyan97

    Why Time is not same when extracting from DateTime

    I want help in getting time value of DateTime from column A as per manual typed time in column C Why there is a difference? ABCDEF1DataExtracted TimeManual TimeTestFormat22:00:00 AM2:00:00 AM2:00:00 AMFALSETime32:00:00 AM0.0833333330.083333333FALSEGeneral (Pasted as...
  16. S

    Help with IF formula

    Hi guys, I really need help to set up a logical solution to this. In 'Value' column I want to have a value based on time interval. If a time interval in a cell in Sheet 1 falls inside a time interval in Sheet 2 then take the values shown in Sheet 2, otherwise 40. See image. THANKS FOR HELPING
  17. charlesstricklin

    Possible to sum up time in mm ss format?

    I'm most likely asking for the impossible, but is it possible to take a column of numbers showing time in a mm ss format and add them all up into an hh mm ss format? Text, obviously. I'm using Excel 2010 on a Windows 10 PC. 35m 22s25m 37s30m 48s2m 59s18m 39s19m 24s38m 39s21m 54s5m 19s17m 8s26m...
  18. J

    Subtotal of Sum of Hours and Not Count

    Excel Version = 365 OS = Windows Hi Everybody, Still New to the whole Power Pivot thing so please bare with me as I will have a lot of Obvious Questions that might seem stupid. Summary: I have Engineers that work on Work Orders, Each Work Order has a Start Date, Start Time and a End Date...
  19. D

    change time every minute

    In column B it prints the current time whenever the value changes in column A. I am using condition like if the current time is greater than> 1 min then prints the value in column a . the problem is I want to run the code exactly at 15 sec every min like 9:15:15, 9:16:15, 9:17:15 ...somehow it...
  20. P

    calculating remining time in hours knowing the parts left and target

    Hi All, I'm trying to write a formula that is going to calculate time to finish parts in hours, for example i have 35000 parts making 550 per hour how long is going to take me to finish it in hours and minutes. I tried changing the numeric to time but its working only in 24h time it doesnt give...

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