+/- 5 Minutes Of A Cell
Jan 6, 2010
I have two columns of different dates and times; I've been trying to write a formula to have excel look at a specific value (A1, for example), and tell me if that date/time is within 5, 10 or 15 minutes of any value in column B. If I could have it highlight the two values, that'd be great, but the most important part is having it come back with TRUE or FALSE results, something which is beyond me at this point.
I've looked at this a number of different ways but I can't seem to get it working.
View 10 Replies
ADVERTISEMENT
May 25, 2011
I have a spread sheet with a colum showing average time to complete a task. This is currently shown as Days:Hours:Minutes:Seconds (4:19:33:19). I meed it to be shown purely as minutes, or at least as hours and minutes.
View 4 Replies
View Related
Dec 5, 2006
I have a formula which will calculate the number of hours and minutes between two military times. I would like it to calculate the total number of minutes instead of hours and minutes. I have uploaded a small example of what i have so far.
View 3 Replies
View Related
Jul 4, 2006
What formula will convert 4.50 to 530 minutes ( "Decimal Time" )
another example 16.50 to 1250 minutes.
View 13 Replies
View Related
Jan 21, 2009
I'm trying to convert 3786 minutes to day:hours:minutes. So divided it by 1440 which is 2.63... but I want this displayed in the worksheet as 2 days 1 hour and 3 minutes (02:01:03), I just can't seem to get it to work and it seems quite simple... but I'm missing something.... I was trying a custom format like dd:hh:mm or [d]:hh:mm and I was also trying a convert function and =day/1440+hour +minute
View 9 Replies
View Related
Aug 22, 2006
creating a formula for converting time data that has been created in an excel spreadsheet in minutes i.e. 516 minutes which I need to turn into Hours and Minutes i.e. 08:36 I am not experienced using Formulas, apologies if this question has been posted before, I did use the search facility to look for threads, but could not find anything related
View 5 Replies
View Related
Jul 4, 2007
I have a worksheet which I am trying to format as a template which includes inputting start times and end times of work and calculating how many minutes are taken to do the job. I just can seem to find the correct formula.
View 9 Replies
View Related
Aug 6, 2008
is there away to format a cell to do minutes and seconds? then get an overall sum?
View 9 Replies
View Related
Apr 23, 2006
I currently have a lot of times saved in an excel file that are in seconds for example 245.9 seconds. Need formula where i could have in the next cell to it where it would say 4 minutes 5.9 seconds.
View 2 Replies
View Related
Jun 18, 2014
cell A1 has the time (09:00), cell A2 has the minutes (60), cell A3 is the sum of A1+A2.
Im using this formula =A1+TIME(0,A2,0) - which is fine, except A1 is sometimes blank, so therefore I would like A3 to be blank.
I thought I could use this: =IF(A1,"","",(A1+TIME(0,A2,0)) But it doesn't work.
View 2 Replies
View Related
May 22, 2013
Data in a cell is formatted in h:mm which is truly a result of a calculation of # of hours & minutes detained at a location. D
Data is result of microstrategy query so result is
E.g. 17:08
Cell is formatted as custom h:mm, but there is actually a fictitious date of 1/1/1900 defaulting in front of h:mm when double clicking into cell or viewing in fx field above. How do I get rid of that date which is inhibiting me from converting 17:08 to minutes by using the formula of =TEXT(L3,"[m]")
View 3 Replies
View Related
Oct 13, 2012
i need a vba code , i have time in column F like 8:30 , 3:30 , 5:30 , 8:30 , 9:30.......i need a macro which will add 00:30 in all cells in column F if time is less than 7.00 hrs
View 3 Replies
View Related
Sep 23, 2008
The below seems to work but I'm wondering if there might be a better way. I'm trying to keep an ongoing up-to- date and accurate time. my code is as follows:
Private Sub Workbook_Open() ' placed inside thisworkbook
Call TimeUp
End Sub
Sub UpdateTime() ' placed in module
If Range("A4") = TimeValue("00:00:00") Then
Application.OnTime Now + TimeValue("00:01:00"), "TimeUp"
Else
UpdateTime2
End If
End Sub
Sub TimeUp()
[a1] = Time
UpdateTime
End Sub
Sub UpdateTime2()
[a1] = Time
Application.OnTime Now + Range("A4").Value, "TimeUp"
End Sub
Does anyone know if there is any way to improve the Code or formulas within the Cells?
View 3 Replies
View Related
Jan 26, 2008
1st post. Very basic understanding of Excel / Macros / VBA but I have searched and still not quite able to get what I'm looking for.
I would like to be able to manually put in a TIME in a cell, and have a macro run at set times before that TIME e.g. something like
If TIME in cell A1 =(hh:mm minus 30mins) run macro 1,
If TIME in cell A1 =(hh:mm minus 5mins) run macro 2
It was suggested to me to use vba code that would constantly check the time against the system time and as soon as it is 30 mins before the time in cell A1 and the 30 mins flag in cell B1 was ‘N’ then it would run macro 1 code and set the 30 mins flag to ‘Y’ to show that macro 1 had been run.
and that this could also do the same for the 5 mins event
View 4 Replies
View Related
May 28, 2008
I have a range that refer to an external data. This external data is refreshed every one minute. So the data is changing every one minute. I need to copy the content of one fix cell in that range into another cell every one minute, each time copy to a different cell. Example: cell A1 has the content that refer to an external data. Cell A1 is updated every one minute. At first A1= 100, I need it to be copied to cell B1 (so B1=100); one minute later, A1=101, I need it to be copied to B2 (so B2=101) and so on.
View 4 Replies
View Related
Apr 1, 2009
I have the foollowing equation in a cell:
=NETWORKDAYS(A2,A12)+G12
My answer is 1081:23:42.
Is there a way to have it show the number of days, hours, minutes and seconds? So it will say 45:1:23:42? (45 days, 1 hour, etc...) Or something along these lines?
View 9 Replies
View Related
Jan 29, 2010
Format Time Cell For Greater Than 24 Hours: Hours & Minutes Only .....
View 9 Replies
View Related
Jan 6, 2010
A column of cells has information about periods of time (XXXhYYmZZs) in text format like this:
65h30m28s
6h3m12s
3h54s
1h4m4s
12m26s
19s
and so on. Minutes and seconds can be 1-59, but hours can be any number. Any variable with ZERO value will not be shown in the cell as you see in the examples above.
how to calculate the number of minutes in each cell.
View 14 Replies
View Related
Jun 22, 2009
I am using Microsoft Excel 2003. My question is about calculating time. 1 hour + 1 hour and fifteen minutes would equal two hours and fifteen minutes. Using Microsoft Excel 2003, let's say I am using cells A1, A2, A3 and A4.
A1 will be 1:00 for 1 hour
A2 will be 1:15 for 1 hour and fifteen minutes
A3 will be my total for adding cells A1 and A2 and the answer will be 2:15 for two hours and fifteen minutes.
My specific questions is: Would it be possible for me to have the fifteen minutes (0:15) from the two hours and fifteen minutes (2:15) automatically carry over to cell A4 or cell A4 of another worksheet without having to type in 0:15 or having 2:15 appearing in cell A4?
View 3 Replies
View Related
Dec 20, 2009
i am trying to make a employee work hour sheet so i can add the time and it add up all the hours and minutes he/she been working . now what i am trying to do is to enter 810 in the cell it automatically change it to 8:10 format but the problem it change it to 12:00:00.
even when i enter 083612 it again change it to 12:00:00.
now i have used the format cell > time and no luck. i already removed and installed my office but i still have the same problem.
View 12 Replies
View Related
Aug 9, 2014
I want an updating field to be copied into the next empty column every 4 minutes and how to do so, with the least amount of processing power to do so. At the same time, I want a time stamp to be inserted above the column.
At first I used "end.Xl" and copy/paste-special, but that was quite consuming of data power.
Preferably it should go to one worksheet to another, without automatic screen updates and such. This is the code I've come up with so far:
[Code] .....
I also tried to get it run at 8:30 every morning, every 4 minutes, until 17:00 but seem to get it to work.
[Code] .....
View 3 Replies
View Related
Jun 9, 2009
I have this code, and it's not working: ...
View 9 Replies
View Related
Sep 19, 2013
I have an issue with identifying start-stop times for special school bell schedule. Cell B2 is contains start time (7:50 AM) and D1 is the establish variable for class length (in this case 45 minutes). Passing time is constant (5 minutes), but needs to be added to the day schedule with the start of each class. I attempted to convert these value to minutes with no luck, same goes to formatting cells.
Hour
Start
End
Class Length
Passing Time
[Code]...
View 1 Replies
View Related
Oct 9, 2013
I have a huge sheet (CSV) with values registered every 1 minute. The CSV is in format: date (dd/mm/yyyy hh:mm:ss) , value
I would like to return the average value for each 15 minutes. The problem is that there are some gaps in the record. For example, there are some days where only few hours are recorded. For this cases, I would like to use the average of the 3 next days for some instant.
Example:
If there are a gap between 01/01/2012 01:00:00 and 01/01/2012 01:15:00
I would like to used the average from 02/01/2012 01:15:00, 03/01/2012 01:15:00 and 04/01/2012 01:15:00 averaged values.
This is quite complex.
I thought about the algoritm. Firstly I think I need to calculate the average for each 15 minutes without considering the gaps (if there are a gap the code should leave a empty cell)
Then the code should find the empty cells and use the next 3 days for estimate the values.
View 1 Replies
View Related
Mar 10, 2008
I have, on some occassions, negative minutes e.g. -125. I have a formula in a separate column which divides the minutes by 1440.
Hence I could have
AE AF
Session Minutes Session Time
210 03:30
-125 ############
Column AE is General format, whilst column AF is Custom i.e. hh:mm.
How do I rectify the formula AE2/1440 so that I get the Session Time to work but with a negative sign?
View 10 Replies
View Related
Oct 7, 2008
I'm trying to write a macro in excel that will save the document every couple of minutes. After searching the forums here for a bit I found something that might work:
Sub test()
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 30
waittime = TimeSerial(newHour, newMinute, newSecond)
Do
ActiveWorkbook.Save
Loop
End Sub
The only thing about this is that it runs constantly and won't stop saving. Is there a way to do this where it will only save every 5 minutes or so?
View 9 Replies
View Related
Oct 21, 2008
I have a cell formatted as general that has need to be able to to take 21:56 and convert that to minutes. That is 21 hours 56 minutes to 1316 minutes. Then I need to add those minutes to a time to come up with like 19:14 + 1316 minutes = sometime the next day.
View 9 Replies
View Related
Feb 19, 2010
I have been racking my brains about this for the last hour without any joy. If I have a time value of say 01:12:00 in cell A1 (which is the difference between two other time values), but I want it displayed in minutes, so it displays 72 or 72:00 instead of 01:12:00 (which is 1 hour 12 minutes),
View 9 Replies
View Related
Jun 28, 2012
I have downloaded a punch in time clock from another user " Alex17", great job by the way. I was wondering on how to apply some certain rules this. I would need the times to round to the nearest quarter. Let's say someone punched in @8:01AM or any time up to 8:07AM, I would need it to round to 8:00AM, if they punched in from 8:08AM up to anytime to 8:14Am, I would need that to round to 8:15AM or if someone punched in @ 8:23AM it would round to 8:30AM....etc. I attached the form.
I need these rules to apply
7:00 - 7:07 round down to 7
7:08 - 7:15 round up to 7:15
7:16 - 7:22 round down to 7:15
7:23 - 7:30 round up to 7:30
7:31 - 7:37 round down to 7:30
7:38 - 7:45 round up to 7:45
7:46 - 7:52 round down to 7:45
7:53 - 8:00 round up to 8
or if this makes more sense
7:00 - 7:07 round down to 7
7:08 - 7:15 round up to 7.25
7:16 - 7:22 round down to 7.25
7:23 - 7:30 round up to 7.5
7:31 - 7:37 round down to 7.5
7:38 - 7:45 round up to 7.75
7:46 - 7:52 round down to 7.75
7:53 - 8:00 round up to 8
View 4 Replies
View Related
Oct 17, 2008
I know similar questions have been asked in the past, but I can't seem to get this to work for my specific case. I need to convert hours into minutes, and these times do not conform to a 24 hour clock. For example, I need to convert 1000:15 into 1000.25
View 3 Replies
View Related
