Adding A Time Offset Based On Time Of Day

Oct 1, 2008

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 5 Replies


ADVERTISEMENT

Offset Real Time Data Based On Validation List

May 8, 2007

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 9 Replies View Related

Adding Time More Than 24 Hours To Date Time Stamp?

May 30, 2012

I want to add hours to a date-time cell to get result in date-time.

Format of cell A1 is d/m/yy h:mm AM/PM
Format of cell A2 is General
Format of cell A3 is d/m/yy h:mm AM/PM

I want to add A2 (number of hours) to A1 to give A3.

The formula I used is A3=A1+Time(A2,0,0)

The formula works perfectly fine when A2 is less than 24, but when A2 is more than 24, the date doesn't get changed.

View 6 Replies View Related

Skip Weekends Adding Time To Date & Time

Sep 7, 2007

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 9 Replies View Related

Adding Hours To Time To Get New Time

Mar 30, 2009

I have a started time of say 8am from cell B3. I want to know what time it will be when I add a full number (hours) from another cell. For example A3+B4 where A3 is 8:00 A.M. and B4 is 4.04. I'm looking to get the a result of 12:04 P.M.

View 4 Replies View Related

Lookup Sunset Time Based Onrecord Date - Incorrect Time Calculated

Mar 22, 2012

Consider this code:

'light eligibility
Dim facb As String
Dim sunset As Variant
[color=green]' check if facility has lights[color]
facb = WorksheetFunction.VLookup(RID, ds, 10, False) 'find facility code
If WorksheetFunction.VLookup(facb, fac, 6, False) = "Y" Then 'facility has lights
sunset = WorksheetFunction.VLookup(tempws.Range("A9"), sun, 2, False) 'lookup the sunset time based on the record's date

[Code] ......

This code checks the need for lights at a facility.

It first checks to see if the facility even has lights by cross-referencing a value in the record with a facilities database.

If it has lights, it then checks to see if they are needed. If the rental goes past the sunset time, then it needs lights. Sunset is determined by cross-referencing the date value in sheet1! A9, with the sunset database.

If it needs lights, variable lghtson is calculated equal to "sunset"-30 minutes.

As I step through this code:

WorksheetFunction.VLookup(facb, fac, 6, False) = "Y" Facility has lights.
Check to see if lights are needed.
sunset = WorksheetFunction.VLookup(tempws.Range("A9"), sun, 2, False)
sunset=0.879166666666667 which is 9:06PM. This is a proper value from the lookup.
If rental_end.value > sunset Then
rental_end (value from textbox) = "9:30 pm" , sunset=0.879166666666667. This is true, and Excel accepts it as true ...
lghtson = sunset - 0.5
0.379166666666667 = 0.879166666666667 - 0.5 (9:06 AM)

This is not the value I was looking for. I was looking for 8:34PM (0.856944444444444)

View 2 Replies View Related

Calculating Time Owed Based On Time Worked

Jun 23, 2009

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 9 Replies View Related

Delete Date & Time Cells Based On Time

Dec 6, 2006

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 9 Replies View Related

Adding Time: Adding Individual Cells Works, But SUM Doesn't

Mar 28, 2008

I have a column of times: e.g. 10:03:00 and I would like to add them all up.

=A1+A2 works fine.

=sum(A1:A10) does not.

View 14 Replies View Related

Display End Time Automatically Upon Entering Start Time And Time Usage

Dec 19, 2008

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 2 Replies View Related

Time Zone Offset Calculation?

Apr 2, 2014

I've got a CSV file that has been sent to me where each entry has a vehicle reports in with certain metrics. Included in these metrics are the time the metric is reported. The problem I am having is that the time is delivered in UTC date/time (in Column A). Column B has an "offset" to show how many hours difference there are between the UTC time and the local time for that particular vehicle.

Problem 1 - My initial thought was to use "Text to Columns" with space as the delimiter, thinking I could just run a simple subtraction formula once completed. The problem here is that if an entry is recorded after 7pm local time, the UTC time moves into the next day, and the simple subtraction formula will not take that into account.

Problem 2 - Even if there was a workaround, it seems that the numbers in the "Offset" column don't work well with time calculations because they are not a time (it's -5, -6, -7, or -8).

View 4 Replies View Related

Adding Time :: 01:54 +00:58

Apr 8, 2008

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 9 Replies View Related

Adding Time Function

Dec 9, 2008

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 2 Replies View Related

24 Hour Time Adding

Apr 2, 2009

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 3 Replies View Related

Adding And Subtracting Time

Jan 23, 2008

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 9 Replies View Related

Adding Time :: Formula?

Dec 20, 2008

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 9 Replies View Related

Adding Time Values

May 1, 2009

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 9 Replies View Related

Adding Time Cells Together

Jul 14, 2006

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 8 Replies View Related

Adding Time Over A Range

Jan 19, 2007

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 9 Replies View Related

Adding Time :: Recognize Format Hh:mm:ss

Dec 17, 2008

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 6 Replies View Related

Adding Lead Zeros To Time

Aug 3, 2009

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 12 Replies View Related

Formula For Adding Military Time

Oct 28, 2009

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 9 Replies View Related

Adding 10 Minutes To Current Time

May 11, 2009

In one of the spreadsheets there is a need to have a 'action at' field.

This is the next five minute time slot between 5 and 10 minutes in the future (eg 10.51 -> 11.00 , 1.56 ->2.05 etc)

View 14 Replies View Related

Adding Time :: By Hours And Minutes

Jun 4, 2006

If i have a start time of 5:00 am and a finishing time of 4:25 pm, what function do i use to get the total time of 11 hours & 25 minutes?

View 10 Replies View Related

Adding Date And Time Fields

Nov 24, 2008

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 4 Replies View Related

Adding And Subtracting Date And Time?

Oct 17, 2011

I've tried a number of examples but I can't seem to get it to work. the desired results in column C.

10/17/11 12:00 PM10/17/11 1:00 PM1:0010/17/11 12:00 PM10/17/11 9:00 AM(3:00)10/17/11 12:00 PM10/16/11 9:00 AM(27:00)

View 4 Replies View Related

Adding Time Racking Formulas

Sep 12, 2006

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 11 Replies View Related

Adding Time :: Dates Should Get Changed As Well

Apr 15, 2009

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 9 Replies View Related

Adding X Hours To Date & Time

Aug 23, 2008

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 3 Replies View Related

Adding Worksheet Code During Run-time

Sep 10, 2004

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 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved