Adding A Time Offset Based On Time Of Day
I am replicating a Matlab program which calculates tide levels at different times of day. I need to replicate it in excel to speed up data analysis and I am nearly there.
what happens is I need to apply a time offset to the time of high tide at port a based on the time of day, so if it is:
between 00:00 and 06:00 the high tide at port b is 81.6 minutes after the peak at port a
between 06:00 and 12:00 the high tide at port b is 74.56 minutes after the peak at port a
between 12:00 and 18:00 the high tide at port b is 81.75 minutes after the peak at port a
between 06:00 and 12:00 the high tide at port b is 79minutes after the peak at port a
I tried this formula, where CO2 has the time/date of the high tide at port a:
=IF(CO2<0.75,IF(CO2<0.5,IF(CO2<0.25,CL2+(81.6/(24*60)),CL2+(74.56/24*60)),CL2+(81.75/(24*60))),CL2+(79/(24*60)))
The problem is the high tide on 07/01/2005 07:45 is read as 38359.32 rather than 0.32 - is there any easy way to tell excel I'm only interested in the time not the date? I have this spreadsheet setup now to do all the other bits required and i is just the timing that is a problem.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Adding Time :: Number Of Minutes Worked Each Day
I import via copy paste into excel from a timekeeping programme the following time I have worked each day, as an example: Mon 7h 55m Tues 6h 30m Wed 7h 24m etc Is there a method of changing this in excel to work out the number of minutes I have worked each day? The timekeeping programme does not let me alter any parameters, so h & m is what I have.
View Replies!
View Related
Time W/in Each Hour Of Day Btwn A Time Range
I need a formula to calculate the time (in hours/fractions of an hour) that is "covered" for each hour of the day between a range of times. In other words, I have a "START TIME" and an "END TIME" and for each hour of the 24-hour clock, I want to know how much time this range covers. For example, if my start time is 3:30am and my end time is 5:15am, for the 3am hour, the formula would return 0.5 hours, for the 4am hour it would return 1.0 hours, for the 5am hour it would return 0.25 hours, and for all other hours it would return 0.0 hours. The range of START TIME and END TIME can be up to 24 hours (but not more), but the tricky part is that the START TIME can be on the day BEFORE the END TIME (e.g., START TIME of 10:35pm and END TIME of 5:45pm the next day).
View Replies!
View Related
Offset Real Time Data Based On Validation List
What I have: I have 12 months in a year and each number represent a month. I have generated lists of data mainly interest and principle payments from 12 amortization sheets which i have just copied into the attached spreadsheet and will change accordingly based on the loan terms which doesn concern us. For our purposes we wont make distintion between principle and payment as i just need to see how to make what i need work. What I need: I need to offset data vertically automatically once i the user selects an option value (Number in this Case) from the validation list located within the Payment control box. Going from left to right you see the raw data but in this example i need the data on the right hand side to be offset down the number of rows which may be found in the control box as a validation list. IE: So if i were to use "Loan I" [L6] and select a "2"[J6] from the "Payment Control" then the data on the right hand side [L6[ would reflect a $0 but instead the new value for L7 would be "$24.96" and would continue down for about 374 rows. This will have to be done for Loans I - Loans N [L6:Q6] respectively. This is all i need in order to compile and make use of in my spreadsheets. Thanks and cannot be a formula as forumlas will be overwritten and cannot be a macro so it must be a module in itself so it an run when the list is changed. the single digit values in coulmns M6:Q3 are there mainly so you can see the difference between the base and the position at which the data is placed at. its not need but is there to stress and to show the number rows in offset of data.
View Replies!
View Related
Creating A Time Card HR:MM Not As Time Of Day
I have a time card that is split into sections so there are a lot of SUM formulas. However, I need to include a space for breaks - 10 min. How do I make everything convert comfortably? Ex. 5 hours allotted to person A. 5 hours to person B. 1 hour 40 allotted to tasks. And 20 min per day for breaks. Is there a way to format the cell so that it displays hours and minutes 1:35 = 1hr35min ?? All the formats available show it as time. I need it as a quantity of time rather than time of day so that the formulas will add correctly.
View Replies!
View Related
Skip Weekends Adding Time To Date & Time
I need a formula to add just the time to ' date and time', ignoring weekends. eg: Fri 24-Aug-07 10:52 is the date and time 28:48:00 is the time If I add the time to 'date and time', result is coming as Sat 25-Aug-07 15:40 But it should come as Mon 27-Aug-07 15:40 (hence ignoring weekend)
View Replies!
View Related
Delete Date & Time Cells Based On Time
I have a large dataset where the first column is date and time i.e. "20/01/2005 03:41:06" and I want to delete certain rows based on the times. I have already tried playing about with macro's but failed fairly spectacularly so far. I have code from someone else to delete cells if the value equals a certain time but this doesn't work as the cell contains the date too. I have already recorded one macro to reformat the data to as the software return 10 timestamped samples per hour and I want 8 i.e. every three hours so there is constant separation for statistical purposes. Both the macros are shown below so you can see what I have. Ideally I would like to replace the line - If (r.Cells(n, 1) = TimeValue("22:41:06")) ............ - with one that reads - If (r.Cells(n, 1) CONTAINS TimeValue........ - but I don't know if that is possible? If not is there a way to separate the time from the date into 2 columns and then I can delete rows based on the time column using the code below? .......
View Replies!
View Related
Calculating Time Owed Based On Time Worked
I know the title is a bit vague, but I cant think how else to word it! I have a sheet (attached) which works out hours worked, and if the amount is under a specified target, it counts how much time is owed. The problem occurs when someone works more hours than the specified target. I guess I need an IF formula of some kind, to say if the figure is over the target, to put zero in the hours owed column.
View Replies!
View Related
Format Date & Time To Show Day, Date & Time
I have a sheet with a list of dates in it that I wish to format to show the day as well as date and time. I am trying to do this automatically with a macro, below is the before and the desired after. Before Format - dd/mm/yyyy hh:mm Appearance - 29/03/2009 00:30 After Format - ddd dd/mm/yyyy hh:mm Appearance - Sun 29/03/2009 00:30 This works fine when I format the cells manually, but when I try to format them via macro '29/03/2009 00:30' becomes 'Sun 29/03/2009 12:30' which is obviously a totally different time! Has anyone got ay idea why it might be doing this?
View Replies!
View Related
Display End Time Automatically Upon Entering Start Time And Time Usage
I have a worksheet which contains START TIME in column A, then TIME USAGE in column B and END TIME in column C. User enters start time, followed by the number of time usage in minutes, how could i possibly display the end time automatically in this scenario? how do you add the entered time usage to the start time to display the end time? Say if I enter 1:00 AM at start time and 00:15 minutes on time usage, how can 1:15 AM be displayed on the end time automatically?
View Replies!
View Related
Time, Day And Criteria
What it does is populate a graph based off the time frame of multiple incidents occurring on a specific day. My goal: To have Sheet2 tables populate the times automatically from Sheet3 based off set criteria. Basically what I need is a formula in the Begin Time and End Time cells of the graph for Sunday through Saturday on Sheet2. I want this formula to pull the lowest time and the highest time from raw data on Sheet3 based off criteria in B2 and D5 on Sheet2. I need for days that Criteria in D5 is not met, to return blank. This way it maps out the overall time of all incidents for criteria in D5 and places the time frame on the days that criteria in D5 is shown. So for example if Sunday, Monday, Wednesday are the days that "Case 3" (Sheet2 D5) shows up on Sheet3 raw data. I want the total time from all incidents containing Case3 to populate on only Sunday, Monday, Wednesday, and leave all the rest blank since Case 3 does not show on those days. Since the times on my graph all end in "00" is there a way to round down the time to end in "00". So if it comes back as 11:50 can the formula round down to 11:00?
View Replies!
View Related
Lookup For Least Time Of Day.
I have tried several formulas (vlookup) but failed. I am trying to capture the earliest time of day for April for each name (Tom,Steve). The results of the formula should appear in cells F5:I6. VLOOKUP apparently would not work.
View Replies!
View Related
Need 3 Results In One Cell For Different Time Of Day
I am pulling what hair i have left out of my head, I am a New Desk Officer for a Local Police Dept trying to set up a patrol report sheet, I need to have a return of "Mids", "Days", "Swings" to be displayed in the cell next to the one labeled "Shift" depending on the time of day. IE: 12am-8am = "MIDS", 8am-4pm = "DAYS" 4pm-12pm = "MIDS".
View Replies!
View Related
Calculate Total Time With A Maximum Per Day
Date Start End Total 12/8 - 2:00 3:00 = 1 12/8 - 4:00 5:00 = 2 12/8 - 20:00 21:00 = 5 12/9 - 23:00 0:00 = 3 12/9 - 3:00 4:00 = 4 =SUMIF($A:$A,">="&TODAY()-1,D:D)/24-SUMIF($A:$A,">"&TODAY(),D:D)/24 In the example above, E2 calculates the totals between 12/8 and 12/9 giving me 15:00. What I want the formula to exract is the total from Col D the total from 12/9 to the previous 24hrs. 12/8 has 2 entries, and 12/9 has 3 entries. The total I want to extract in 24 hours, NOT 1 day, is 14. What formula can I use that will calculate the totals in 24 hrs.
View Replies!
View Related
Calculate Time Difference That Spans Into Next Day
I'm trying to do some calculations involving times. I'm using the format [=A2+(A1>A2)-A1] in order to calculate times from one day to the next which avoids negative numbers. This is working well. My problem is now that I'm trying to develop my spreadsheet and am trying to embed this inside an IF statement as the [value_If_True]and I get an error because it doesnt like the leading equals sign inside the IF statement.
View Replies!
View Related
Play Sound File At Set Time Each Day
I'm trying to automate/keep records of my day by setting up a spreadsheet which : 1) Plays a wav music file at a schedule time as a reminder and 2) at the same time calls up a form where I can enter the results of the activity then 3) saves the results to a sheet where consecutive daily records can be recorded and graphed. Example At 5am each day I want a "Motivation.wav" music file to open and play and a form appear where I enter in the number of sit ups, push-ups I get done, which I enter in the form and it gets saved to a sheet where I can graph my progress based on the results recorded.
View Replies!
View Related
Trying To Capture Point In Time Data In New Fields Each Day
Trying to capture data from specific fields (which are populated with live data collected elsewhere) into new fields based on date. Ex: A1 - A4 have "totals" derived from an external source, and the fluctuate daily. I want to take today's totals and drop them into D1 - D4. Tomorrow, I want totals dumped into E1 - E4, the next day they go into F 1- F4, and so on... in other words, I'm tracking daily totals over time. I've started with a macro that I would attach to BeforeSave as follows--
View Replies!
View Related
Run Macro In Closed File At Same Time Each Day
i know there is information on this ontime method, i haev read through it, but since i am working with reuters running live in the background they just dont work. Anyway, on a normal excel sheet, I have made the automated macros run on a timer, which is great. BUT If someone switches off my pc, its not going to be so great because i dont think the macro would run without excel being open. QUESTION: How can i make my macro run every morning at 8am? Assuming the worksheet is closed, even excel is closed. i am sure the answer is starring me in the face, i am just getting boerd of looking at it! in the Thisworkbook: Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnTime dTime, "thismodule", , False End Sub Private Sub Workbook_Open() Application.OnTime Now + TimeValue("20:00:00"), "thismodule" End Sub and in the Module: Sub thismodule() dTime = Now + TimeValue("08:00:00") Application.OnTime dTime, "thismodule"
View Replies!
View Related
Adding Time :: 01:54 +00:58
I have read alot of the online instruction on how to correct what i am doing, and its not working!!!! Im getting so frustrated. I have a column of time in hh:mm format. There are 130 entries that i need added. The sum keeps coming up as 19:44 which i KNOW is incorrect because when i review the hours alone they are more then that. How does excel handle, lets say 01:54 +00:58.
View Replies!
View Related
Adding Time Over A Range
I need to add times of employess over a 2 week schedule.I have used a string variable to select the rows and columns as employees change.How do i add the times in this case.Sample code is below: j = 12 k = 20 For i = 16 To 276 row_str = "A" & i & ":B" & i Windows("TimeSheet.xls").Activate Range(row_str).Select Application.CutCopyMode = False Selection.Copy Windows("Report.xls").Activate ...................
View Replies!
View Related
Adding And Subtracting Time
I have the formula in my spreadsheet to compute time. It works only if the time in B1 is greater then the time in A1. I would to know if there is a formula to compute time with either negative or postive answer. For example if a carrier was set to load their papers at 12:02pm and ended up loading early at 11:50am I would like the result in C1 to be -12 or (12). Currently I am using the formula: =HOUR(B1-A1)*6+MINUTE(B1-A1). Like I said, it is all good until someone loads early and then I get a "#NUM!" Are there any other formula's that I can use or is there a simple modification to the formula I am using?
View Replies!
View Related
Adding Time Values
I'll admit that the database I'm currently working on is not a vital on - it's my CD collection but I do use it as a testbed for spreadsheets at work so please bear with me! I'm trying to add running times of CDs together, but of course can't simply use SUM as it will assume the values are base 10 I've got some extra columns in to separate the minutes and seconds, then for totals to divide the seconds by 60, add the result to the total minutes and finally add the remainder of seconds to this but........ is it possible format a cell so it won't allow erroneous time entries (ie, if I tried to put in 45:65 it would reject it?)
View Replies!
View Related
Adding Time Function
I want to add two times together. 14:00 (time) + 03:42 (duration) = 17:42 Is there vba code to be able to do this or an excel function? I have tried searching but strangely couldnt find anything?
View Replies!
View Related
Adding Time :: Formula?
I have data that is exported to an excel spreadsheet from the phone switch for Average ACD time and Average ACW. Example: one cell is value :07:01 and and the next is :00:42. I want to add the two together to get :07:43 or 07:43 for AHT. I have tried formatting the cell with custom time and time formats, but I get #Value. I believe the ":" at the beginning is not recognized. What formula can I used to get the two cells to sum?
View Replies!
View Related
24 Hour Time Adding
I have a start time in cell A1 (say 9am entered as 900), cell B1 has a time interval (25min), Cell C1 gives total (925). Cell B2 has the next time interval (56min). How do I get cell C2 to give total of 1021 rather than 981? Values in columns B and C continue on down.
View Replies!
View Related
Adding Time Cells Together
1) I am trying to find a way to add together the total number of hours worked from shift start time to the end of the shift start time throughout the week. 2) Add together the total number of hours travelled. again from the start and end times throughout the week...(presumably, if I solve the first problem, it in itself will provide the second solution.!! (Grin) Where shift start and end times are concerned, the cells are formatted as time, but I want the sheet to produce the results of the total number of hours worked, so that I may cost the hours and make payment. Ie: 62 hours worked = £ at such and such a rate, and 25hrs travel paid at £ different rate.
View Replies!
View Related
Calculate Work Hour Phone Time By Day, Week & Month
I am trying to figure out a way to count the minutes used from 8am-5pm Monday-Friday. I have this years worth of data. I would like to do it by month and by week and by day. I use office 08 for the mac and its my understanding that it doesn't have VBA. I would also like to be able to figure out if on a certain date a employee made over x amount of phone calls in a day. But have several employees. I have columns that are labeled date, employee, minutes used.
View Replies!
View Related
Adding Time :: Recognize Format Hh:mm:ss
I am importing from another program into Excel. Anytime I have a time in a column with a 0 in the 2nd hour place it will not add that time in. Example (05:21:32) A1 10:20:12 A2 05:12:15 A3 10:15:12 Total= 20:35:24 How can I get excel to recognize a time in this format with a zero (05:34:23)?
View Replies!
View Related
Adding Lead Zeros To Time
I'm pasting data from an external source into Excel. The data is in hh:mm:ss format. However, when the source data has zero hours it only displays minutes and seconds preceded by a colon. For example, 5 cells of data in a column might look like this: :29:28 :31:50 1:15:17 :05:47 1:19:36 I need to run simple calculations on these times, but the cells without an hours value paste into Excel as text format. What are some ways to add a zero before the colon in Excel?
View Replies!
View Related
Adding Time :: Dates Should Get Changed As Well
I am having a 2000 records of date and time in excel (see the below example). My query is I want to add 6 hrs to each Cell and accordingly the Date should get changed for e.g see the B Column after adding 6 hrs the data should look like this. I want an excel formula (don't want VBA Code) ...
View Replies!
View Related
Adding Time Racking Formulas
I want me to track our inbox. All jobs that go into the inbox get logged on a sheet and at the end of the day get transferred to an Excel spreadsheet. They now want to track how long s job sits in the inbox before it's logged out and worked on. Currrently we put a time and date for both logged in and logged out. I've attached below a sample spreadsheet and a brief description as to some restrictions. Our hours of work is 7AM to 6PM. So we only want to add add up the time the job was in the inbox based on those hours.... ie if a job came in right at 6PM at night and was logged out at 7:05AM in the morning, the total time that job was in the inbox was 5 mins. Same thing applies for the weekends. I'm suppose to be in charge of creating that part of the spreadsheet. ************************************************************************>Microsoft Excel - work time.xls___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB3= ABCDEFGH1********2*Job*#Time*inDate*inTime*outDate*outTime*spent*in*inbox*3*111:15*AM09/11/062:30*PM09/11/063*hours*&*15*minutes*4*24:25*PM09/11/069:45*AM09/12/064*hours*&*30*minutes*-*overnight*5*34:45*PM09/08/069:35*AM09/11/063*hours*&*50*minutes*-*over*weekend*6********7*hours*of*operation*are*7am*to*6pm***8********9*Explaination*or*#3*from*above….******10*Job*came*in*@*4:45*PM*on*Friday*but*was*not*removed*from*th*inbox*until*9:35*AM*on*Monday*morning.*11*Since*our*hours*of*operation*are*from*7AM*to*6PM,*the*time*that*the*job*was*sitting*in*the*inbox*was*3*hrs*&*50*mins.*12*This*is*figured*out*by*4:45PM*to*6PM*is*1*hr*&*15*mins*and*7AM*to*9:35AM*is*2*hrs*&*35*mins.**1hr,*15*mins*plus*2hrs,*35 *mins*equals*3hrs*&*50*mins*13********Sheet1* [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View Replies!
View Related
Adding Date And Time Fields
From an outside source, a date is brought into one cell, and time is brought into another. Is there a way to combine them into one cell with the format of m/d/yyyy h:mm? I tried concatenate, but that did not work.
View Replies!
View Related
Formula For Adding Military Time
I have four columns. In the first one I want to keep my START time (in format of military time), in the second column I have STOP time, in the THIRD column I have TIME USED in other words it is a DIFFERENCE between STOP and START and I used this formula for that: =B2-A2 and it works. Now in the FOURTH column I wish to have the TOTAL TIME added from the THIRD column and this where the problems starts. I used this formula =SUM(C2:C500) and doesn't work correctly. Let's say I have the following data: ....
View Replies!
View Related
Summing Adding Many Time Cells
I have a spreadsheet with many rows of time totals as the following example: Cell 1 - 08:00 AM Cell 2 - 1:00 PM Cell 3 - 5:00. Cell 3 is just the total, and I have no problem with this aspect... However I have two columns of the above format side by side... and the only way I can sum Cell 3 on both columns is something like the following: =sum(c1+c2+c3+f1+f2+f3...etcetcetc. This can end up in a large formula, and I was just wondering if there was a more efficent way? I tried the following but get a #value! error. =SUM(C1:C6)+(G1:G6)
View Replies!
View Related
Adding Time And Date When Saving WB.
See the attachment. When the macro runs. The workbook is saved in a folder with a new serial number. That is fine but I need to debugging the code. Since I need to save the WB name with the time and date to be also picked and named when saving. Refer to cell B1.
View Replies!
View Related
Adding Text Values Representing Time
The times listed in column A are formatted as text. When trying to add them using a simple SUM formula (=SUM(A2:A5)), a false result of 0 is returned. How can we add text values correctly? Solution: Use the SUM and TIMEVALUE functions as shown in the following Array formula: {=SUM(TIMEVALUE(A2:A5))} To apply Array formula: Select the cell, press
View Replies!
View Related
Adding Code To A Userform At Run-time
The ultimate goal is to create a Userform at run-time. The problem is writing event code for that Userform. The current situation is: I have a userform. (Created with the VBE, not at run-time.) There is one command button on that userform. The code module for that userform is this Option Explicit ...
View Replies!
View Related
Adding Worksheet Code During Run-time
A procedure residing in the "ThisWorkbook" module has just added a new worksheet into the active workbook. I now want this same procedure to write a set of code into this new sheet's code module. Solution 1: Create a worksheet before run-time that is already set up with the code you want behind it. Then, use this worksheet as a sort of "template" when you want to create a new worksheet as opposed to using the worksheet.add method. Question: I can use solution 1, but isn't there a way to insert the code on the fly?
View Replies!
View Related
Adding X Hours To Date & Time
I have a problem concatenating time in excel if it is of Date + Time format.. What formula do I use to just add the time by, say, 3 hours? I'll need to use, say cell B2, which will add 3 hours to it.. What formula do I use?
View Replies!
View Related
Adding Time To A Date Excluding Weekends And Holidays
I've been reading up on Excel's date and time functions and can't really figure out the best way of doing this. I have a total amount of time that a machine should take to finish a task. I'd like to enter a date and time into a cell (Start Date) and have another cell return the date and time that the machine should be done with the task excluding weekends, and holidays (End Date). This would be based on a certain number of "working hours" (hours in the workday minus break periods) that would be calculated in another cell.
View Replies!
View Related
Adding Years, Days, Months To Date/time Format
I have some cells which must be in the format 15/06/2007 15:25 I then need to add either days, months or years onto it. Say the above date/time is in cell A1, when I do =YEAR(A1)+5 it displays 2012 if I choose the general cell format, but when I select the same cell format (date time) it comes out as 04/07/1905 00:00
View Replies!
View Related
|
TRACKER
