# time based formula

1. ### count of time ranges in other time ranges

Hi guys, I'm struggling with formula, which will count the number of operations "name" did simultaneously, even partially. I'd like to have a 1 or 0 information in new column (1 = the time interval exist, even partially, anywhere else). Data looks like this: <tbody> name start end Mark...
2. ### Convert x Days y Hours z Minutes to Minutes

Need to convert something like: 4 Days 16 Hours 32 Minutes to something that can be added up or manipulated. Minutes is fine.
3. ### If time is 1:00 AM throws value if false

Situation: Simple nested IF statement that looks for time in one column and returns value in another column. Formula: =IF(E46=TIME(3,0,0),I46,IF(E46=TIME(2,0,0),H46,IF(E46=TIME(1,0,0),G46,IF(E46=TIME(0,0,0),AD46,IF(E46=TIME(23,0,0),AC46,AB46))))) Hint: This formula is in column AF Expected...
4. ### Time formula with conditions

Hi, I have two columns, one with a date and time (column A) and one with a status (column B). I want a third column(Column C) to say if the date and time in column A is less than today's date and the status in column B is not equal to 'COMPLETED' then move the date to today's date at 07:00:00...
5. ### Adding up time from timestamps in excel

Hello, I am trying to add up the total time differencebetween certain time stamps and when I do, I’m not sure how to convert thenumbers into hours for to get the correct sum. I have tried custom cellformatting but that doesn’t change the way excel sees inside the cell formulas.Below is the table...
6. ### Deriving MODE from time

Hello - I am trying to derive the MODE of a sequence of times. I have the following format to convert: 12:00:00 EDT I imagine it does need to be converted Into text or number format so as to derive the MODE of this sequence. I have tried several approaches to no avail. I also wish to derive...
7. ### How to tell excel that time 0000 is greater than 2300?

Need: A schedule in excel is required for a large team. Schedule gets changed every month and people are rotated (mid person will do the night and night will be moved to morning etc). There are five shifts in a day, each shift having 5 teams and each team having at least 8 members...
8. ### Calculate average times

Hi, i'm having a hard time working out average pageview times on a website. I've already had a look on a number of posts but nothing seems to work, i've tried right-clicking the data range and formatting to time or custom as hh:mm:ss as my times are displayed as hours mins secs. I've tried the...
9. ### Changing hour formats to useable excel time formats

Hi all, I have a document that I need to convert large amounts of numbers that have been put into the decimal hours format (e.g. 0.25 hours). But I need to change this to a the number that can be formatted into the excel time formats (e.g. 0.25 hours = 10.2 minutes and or 0.0069444 as a...
10. ### Conditional Formatting Cells X amount of minutes to previous cell

Hi Everyone. I am trying to create a worksheet that highlights SLA acceptance and breaches. In Column F, I have the time an Incident is received e.g. 16:05:00 In Column G, I have the time I accepted that incident e.g. 16:10:00 I need the cells in column G to highlight Green if it's within 15...
11. ### Added time + NOW within the working hours

Is it possible to create a formula that the added time calculated within the working hours? example: if there is on Friday added time, that he does not count weekends stops friday 16u30 but continue from Monday 08u A1 = the added time A2 = the time now + the added time <tbody> A 1...
12. ### using the Date to turn on and off a formula

is it possible to use dates in a formula to activate a formula and then at a designated time stop the formula from working but keep the results of the last recording? I have a report that I pull data from on a monthly basis. what I would like is for the report to automatically pull all the data...
13. ### using time formulas to activate a formula and then stop it

Hi, can anyone help me? I m trying to create a formula that will pull data from a data set but only show the data at the start of a month and then at the end of that month lock the data in place so that another cell will show the data for the next month. the formula I have is as follows...
14. ### How to list multiple, repeating times in a column?

Hi I need to make an Excel sheet to make an animal observation paper. The list requires to observe the animals every 30 seconds so the list would look something like: 8:25 am 8:25 am 8:26 am 8:26 am 8:27 am 8:27 am etc... Is there a shortcut on how to make these times without having to...
15. ### create automated time and replace few keywords excel formulae

i want to get automated add time for every 00:01:05, example 00:00:00,00:01:05,00:02:10,00:03:15. like this i want upto 5 hours.... example : <tbody> A(original) B (time changed) C ffmpeg -i zxcv.mp4 -ss 00:00:05 -t 00:00:7 -codec copy output.mp4 ffmpeg -i zxcv.mp4 -ss 00:01:10 -t 00:00:7...
16. ### Returning Value from Time Ranges

Hi Everyone I have a data table that shows a list of times Trailers were tipped, from which site in time order such as <tbody> Date Despatch Site Time of Trailer Unloaded 28/02/2016 MD01 06:13 28/02/2016 KA09 06:56 28/02/2016 MD19 07:42 </tbody> I then on a separate sheet have a...
17. ### Values in decimal, need result in hours and minutes

Hi, I have a report which throws out a column (A) with projected hours and a column (B) with actual hours worked. These figures are in decimal form. I need column C to display A minus B in hrs and mins. My problem is that the values in A and B cannot easily be formatted to hrs and mins. In A1...
18. ### Errors after midnight in formula calculation

Hello All, I am having an issue with accurately counting hours worked. Now, my formulas all work fine until the ending point of a shift reaches the AM. For Example: 6PM - 1AM would be a 7 hour shift, but the result is negative 17 hours. Now I am using two different set of formulas that are...
19. ### Convert mm:ss to total s, formatting issues

Hello! I have entered most of my thesis data into excel- and I am looking for a way to avoid re-typing all my columns... I am using Microsoft Excel 2010 I entered times into a column (intended to represent time elapsed, mm:ss) but excel auto-formatted it to time of day (hh:mm:dd). It shows in...
20. ### Timesheets with Unpaid Half Hour

Hello All, I need a timesheet that deducts half an hour when the person works after 1pm, basically lunch is 12.30pm to 1pm and if they take it or not 30 mins are deducted. Therefore the formula needs to deduct half an hour if the time if after 1pm. For information i am using military hours...