time & date

  1. I

    Power Query Data types and excel time format issue

    So, I have a column for time duration in my data from different excel files and tables which gets consolidated in power query... But the data in the time duration column from these different files/tables is not the same, it's in these below 3 example formats: 00:24:02 00h 24m 02s 24m 02s 01h...
  2. L

    Add original hour column to accompany date column without datetime.

    Hey everyone, I am trying to add a new 24-hour time column to a one column table that contains only dates. The Date column is dynamically pulled from a database that provides dates for 30 days into the future. I am doing projection work, so there is no original time column for me to pull from...
  3. L

    Error with making hour column from datetime column

    Hey everyone, I'm experiencing a problem with Power Query making a mistake in pulling an hour from a datetime. In the original table, the "Hour" column (2nd from left, in 24 hour format) is not there. I added that column by highlighting my datetime column in Power Query, going to the "Add...
  4. K

    Conditional Formatting data in a Query Table based on elapsed time.

    Hello all, I have a table (Query 1) which pulls data from an SQL server and auto refreshed every 2 minutes. This table has rows each depicting a single order with columns showing various information about the order (Value, Status, Allocation etc) In this table i have a column called "Last...
  5. 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...
  6. F

    Formula for Date\Time

    Hello, Can someone please help me with the below, I'm looking for a formula that would give me the information below. Thanks in advance If column A is Monday - Thursday and time is 9PM to 6AM then Column B = Afterhours, if not then Column B = Business Hours If column A is Friday and time...
  7. 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...
  8. M

    If Else statement with time range for binning purposes

    Hi, I need to perform binning on a time range using if else statement. Can anyone help me with the formula? I can't seem to get it to work... Attached is the excel screenshot to help show what I am trying to accomplish. Thanks.
  9. L

    How to create a detailed performance overview (Maybe a graph/chart)

    Hello everyone, I am currently trying to create a detailed overview of transactions executed by a range of different users in the hopes of creating a easy to understand overview of performance. Which will include the points listed below. In this performance overview I want to include several...
  10. 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...
  11. WildBurrow

    24 Hour Time/Date Formatting Loophole

    I'm using a custom cell format in two cells (mm/dd/yy hh:mm). These cells are used to calculate elapsed time. Users are instructed to input 24-hour time (e.g. 1:00 PM is 13:00); however, they are managing to bypass my attempts to prevent the document from being saved with an invalid date...
  12. L

    Finding duplicates within 5 seconds

    I am unsure if this can be done, I have searched around and nothing quite his the mark. What I am trying to do is write something that looks at the date and time columns and if they are within 5 seconds of each other will look at two other columns within the data and if they are the same...
  13. T

    GANTT schedule - Take value if between time and equipment criteria match

    Hi, I need to make a GANTT schedule from the production with tanks etc. I cannot get the different formulas to work, when they do I will add it as conditional formatting in different colours. In D2 "Gannt Mrexcel.jpg" I want TRUE if C2 find a match in H:H "Raw data Mrexcel.jpg", also the date...
  14. R

    Converting whole number to HH:MM:SS

    Hi! New user to Power BI here and trying to learn how to convert a whole number to a HH:MM:SS format. I've done quite a bit of research but can't seem to find an exact solution online for what I need. I'm currently screening transaction ledgers and the date/time stamp is significantly important...
  15. J

    Calculate finish date excluding non working hours + weekends

    Hi, I'm trying to calculate the finish date and time of a production run but excluding hours the factory doesn't work. I have the start date + time and the production hours needed, please help!!!!!
  16. J

    How to find gap length between date/time?

    Hello, I have the following spreadsheet: I'm trying to figure out a formula that finds a gap when no events are valid. So in this case, there are no events valid from 6:00a - 6:03a. I want a way where excel tells me that through a formula in addition to any other gaps that there may be. Thank you!
  17. C

    Calculate total hours from two entries, with in work hours, skipping weekends.

    Recently found this forum after googling for different how-to's. Came across different tutorials that were a little over my head which includes NetworkDays. I am trying to have 2 date entries with time and date formatted like below, that will add together the hours worked within a working hours...
  18. R

    How to split time by a setted date?

    Hi, I have a lot of remote camera data, and I would like to calculate the working hours of each camera in each season. I divide a year into two seasons: the breeding season (Apr - Aug), and the non-breeding season (Sep - Mar). Column J (Working hours) shows the total working hours, which are...
  19. L

    CALCULATE TOTAL HOURS.. INCUDING ALL DAYS..

    ACTUALY. WE ARE WORKING FROM 06:00HRS TO 22:00HRS DAILY.. WE RECEIVING RAKES DAILY... IF IT PLACED FOR UNLOADING START TIME IS AT 01-01-2022 14:00HRS.. UNLOADING WILL BE COMPLETING END TIME ON 02-01-2022 08:00HRS.. AS PER OUR WORKING HOURS TOTAL TIME TAKEN IS 8 HOURS.. MEANS 22:00HRS TO...
  20. K

    Timestamp (VBA) when a cell containing a formula changes - Error

    Hello! I have an excel file containing a collection of costs and what I would like to do is to have a timestamp updated in cell B2, whenever the total sum of cost is updated in B4. B4 contains a SUMIFS formula to add all the information entered in that sheet. Now I searched highs and lows for a...

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