I have an excel sheet which connects to an SQL database, one of the colums (A) is the date & time for a particular event. I would like to calculate in another cell the difference between the current date and time and the date & time in column A in minutes.
View 3 RepliesI am attempting to pick up a date with time entry on a worksheet and place it into a TextBox on a UserForm. Format on the sheet is mm/dd/yyyy h:mm AM/PM. The UserForm is placing the value as mm/dd/yyyy 12:00 AM. here is the
Private Sub UserForm_Initialize()
If Not Range("dDate").Value = "" Then
TextBox2.Value = Range("dDate").Value
TextBox2.Text = Format(DateValue(TextBox2.Text), "mm/dd/yy h:mm AM/PM")
Else
TextBox2.Value = ""
TextBox2.SetFocus
End If
End Sub
"dDate" is the named range where the date is sitting. The format is also set on the TextBox2 exit event. Can anyone see why only the date portion is being transfered with the default 12:00 AM for no time component of the value?
Cell C2 has the date - "3/03/2014"
Cell F2 has the time - "12:43 AM"
I have combined these in cell O2 - "3/03/14 0:43"
In another sheet 'ref' I have the days split in to two shifts (two 12 hour shifts - from 6am to 6pm (L2), and 6pm to 6am (M2), and the shift that the date falls on is in N2.
So if C2 = "3/03/2014" & F2 = "12:43 AM", it falls between 2/03/2014 6pm and 3/03/2014 6am, then I want to return the third columns value (which shift it is) in the ref sheet. I think this requires Vlookup.
I have a centralized document that myself and my co-workers use. I would like to insert a date formula that upon opening the "Main" document displays the current date (that is no problem). The catch is, when myself or my co-workers make a change to that document, and "save as" I would like that date to stick (no longer display the current date, but the date in which it was "saved as").
View 12 Replies View RelatedDoes anyone have a formula (or formulae) for working out the following:
Don't hesitate to ask me more details, or contact me on MSN...
I have a worksheet with one column showing date and time, in HH:MM (North American Format) and a second column showing temperature readings taken every 15 minutes.
What I am trying to calculate is the total amount of time (in HH:MM) that the temperature was equal to or below a certain threshold.
I have seen some formulaes for calculating elapsed time, but nothing which can account for this variable.
I am creating a formula that matches a particular date with a particular time. All of the dates that have 14:00 (2:00 PM) next to them should be listed from oldest date to newest date. The workbook where the formula needs to be entered contains data that is constantly shifting and updating so the formula has to be based upon the 14:00 (2:00 PM) and not the number of rows because the 14:00 (2:00 PM) row won't always be in the same place.
See the attached spreadsheet for a better explanation. There are two examples of what I need on one sheet to give an example of how the data can shift. Also, the weekend is not included in the data so the dates skip days during that period.
Is there a formula that can be used to combine a date and a time that will be formatted correctly?
For example if I have the following values:
A1: 08/03/12 (Date)
A2: 16:45:39 (Time)
And then I want A3 to be 08/03/12 16:45:39 as the Date/Time. Can this be done with a formula?
1.The attached file shows an example extract of a data extract that has thousands of lines. See the Data Tab.
2.What I need is some time that has passed between two dates in a DDHHMM format
3.What I then need is the top 50 of each of the times (or the longest time past)
4.The column headers will be: WO Number | Contractor Name | Time Calculation – see the various Report tabs
5.The calculations in the Data tab are between the following columns.
a.Difference between Column Z and Column AC
b.Difference between Column AC and Column AD
c.Difference between Column AD and Column AE
d.Difference between Column AE and Column AF
6.Is it possible to show the top 50 only in time?
This report is forming a part of a larger report and all other formulas are already present so I am hoping to keep the file size quite small. The aim is to then hand this over to someone else to just print on a monthly basis depending on the data that gets added with minimum input.
I have been asked to make some ammendements to a workbook currently in use.
I have to calculate elapsed time, and my formula is giving me odd results. I have a start time (cell B5), and an end time (cell F5). The formula being used was =IF(F5>0,((F5-B5)*24)) which gave the correct result. The cell is formatted as general. It didn't alot for the situation where the end time has not happened yet.
I changed the formula to =IF(F5>0,((F5-B5)*24),(K5-B5)*24) where cell K5 is the current time. The results are coming out with 6 - 9 decimal places. It makes no sense as I've tried setting the times to be exactly 24 or 48 hours to the minute. I've also tried replacing K5 in the formula to now(),
what type of vba code I could use to populate a static date and time that I could plug into a formula. Basically what I need is:
IF A1<>"" then L1 shows the date and time of the entry. If A1="" then L1 remains blank. I don't want the date to change once it has been entered.
way to get a formula result to freeze or remain unchanged AFTER a certain time?
I tried using a macro which runs when the file is opened and does a crude 'copy/paste values' operation and replaces the Formula results with their values, but its is not very 'nice'!
I need a table to change a pattern of values but not in the past, but still to remember what that value was....!
I am having a hard time with this formula. How can I adjust my formula so that everything that falls outside the boundries of my given date range will have the word "DELETE" next to it? Here is what I have and it is not working....
View 9 Replies View RelatedI need to enter a formula that calculates the time a report is received from the time it was recorded in our database. therefore, it needs to exclude non working hours. here are the fields:
A1 2008/10/10 16:30
B1 2008/10/11 09:30
C1 8:00
D1 17:00
A1 = report received
B1 = reported recorded in db
C1 = work day start time
D1 work day end time
Where the answer should = 2 hours.
I am not an experienced excel user and so far the only formula I have now is: =TEXT(B1-A1, "d:hh:mm")
And how do I account for weekends?
I have loaded a .csv file in which the first column contains date/times, e.g. 01/12/2012 00:00. How do I now tell Excel (2010) that this is in fact a date/time format? If I select one or more of the cells, click on the Number dialog box launcher and try to pick a suitable format tghe cells resolutely refuse to budge from being text (i.e. left-justified, still allows me to edit the 'seconds' component to a number > 60). Also which data type should I be using? The only one that appears to have a full date/time format listed is Custom (not Date or Time).
View 4 Replies View RelatedHow to create a formula to determine the longer period of time between two date calculations and enter the longer period of time in the cell.
My Spreadsheet:
Cell M2: Birthdate (entered in cell as 11/1/2004)
Cell J2: Last date of service (entered in cell as 10/31/2011)
Cell G2: Calculation to enter the longer period of time between (M2 (year) + 21) and (J2 (year) + 7)
Example using information from above:
2004 + 21 = 2025
2011 + 7 = 2018
2025 is the result I want added to G2
If there is any way to include the month/year in G2 that would be ideal.
I have a column of values resulting from subtracting a static date and time from the current date and time.
This means it is constantly updating, which makes it impossible to sort.
All my work depends on sorting those values, though.
I import data from a program that exports dates and times as text. I have been successful using "text to columns" to separate the time from the date and then using =text(A1,"00:00")+0 to get the time to show as serial time but I'd love to be able to do the whole date/time string in one step. In cell A1 there is data that is general format and is in this format:
01/01/13 00001
No matter how you try to format it, it is not a date or time. For this project I need the serial number for the date/time. Any formula that will format it as date/time and then allow it to show as a serial date/time?
I have 04/02/08 12:00:01 AM (mm/dd/yy hh:mm:ss AM/PM) in text format in a cell. I need to convert this to date/time custom format as given above so that I can make comparisons with NOW() output.
View 2 Replies View RelatedI am using Excel 2013. Anyway, the first issue is that I need to pull a date and a time period from text. So, for example, if I see something like Sunday Prime Time 7/6/14 8:37PM, I would want to pull ONLY the "7/6/14 8:37PM" out of it. Each text box could potentially be different, so it might not always be in the same format as "Sunday Prime Time 7/6/14 8:37PM" it might only show just the date and/or the time without all the extra text i.e. 7/6/14 8:37PM. Some of the cells will have text, others might only have just the time or even just the date and the time. The only thing that I am worrying about in each cell is extracting just the date and time. If this is too much to ask of excel, I would be ok with extracting ONLY the time - 8:37PM and not the date, but I would much rather be able to get both the time and date.
THEN, onto part two of my question. After I would pull the dates and times, I need to compare them with each other. So, when I have the same date with two separate times on that date, I need to write a formula to show if those times on that date are less than 30 minutes apart. So, if I have 6 times on 7/6/14, I need to know if any of them are less than 30 minutes apart.
I would need to have the formula say something like "Problem" if the times on 7/6/14 would be 5:30PM, 5:48PM, 7:00PM, 8:00PM, 8:15PM, and 9:00Pm for example. I would like to see the word "Problem" since 5:30PM and 5:48Pm is only 18 minutes apart, and "Problem" after 8:15PM since that is only 15 minutes past the 8:00PM which is obviously under 30 minutes. The times that are more than 30 minutes apart such as 7:00PM and 9:00PM for example are more than 30 minutes apart from any of the other times that were extracted.
What is the code i need to use to assign a macro to a command button which inserts the current date and time in the selected cell regardless of where that cell is?
View 5 Replies View RelatedI'm trying to write a VBA code for converting the date/time as text to a date/time value.
The scenario is, I have three different types of text :
ddmmmyy
HH:MM
ddmmmyy:HHMM
I could have the code working fine with the first two types, but not the last one.
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?
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)
I am looking for a macros VBA where a user insert or update a data the date and time should be insert in column I and save the workbook.
Note: If the column I already have the date and time inserted before then it should give message record already have date and time.
I am using office 2010.
I need an IF formula, that states if a date is more than a month after the date in the cell then it is timely and if it isn't then it is not timely. For example the date in A1 is 12/11/05, if the cell is A2 is 13/12/05 (or any date after that) then A3 should read Not Timely, if A2 is 12/12/05 (or any date before that, including a date before the date in A1) then A3 should read Timely. Is there any way to do this?? At the moment I am having to go through manually and put in either Timely or Not Timely.
View 2 Replies View RelatedI have a workbook with macro button to run some vba, if i want the button to disable when the user make copies of the workbook (date created is later than original wb saved time) what codes to add in the VBA?
what i assume is to add the time to a cell when the wb is saved, when the wb opens, it check for that cell if the same with the date created, if different, then disable the macro button.
I have a time column (A) that when looked in the cell only shows AM & PM times, but the cell itself (not showing) contains dates too, keeping me from be able to do a sheet wide sort of time or time frame occurrences.
Can I do some thing to sort these cells with their corresponding rows based on time only disregarding dates?
I am trying sort out all rows that in column (A) is time equal to or greater than 4:00 PM OR even maybe sort all rows that column (A) shows a time between 4:00 PM & 7:00 PM. The date in the cell is the problem, I think. Excel 2013
I'm trying to look up information in "pi" by entering a time that you want to look up say 1800 or 935 and have a cell that would enter it as todays date with that time so I can use it as a timestamp in "pi"
View 1 Replies View RelatedI have one column with as many as 50,000 or more rows. The data format for each row/ cell is unique as shown below ( date and time). I wanted to split the data as shown in "Formatted Data" below. Have Tried Text To Column formatting but didn't work right.
Raw Data: Formatted Data (2 cells):
2005/11/02 23:55:15.758 ==> 2005/11/02 23:55:15.758
2005/11/02 23:58:16.698 ==> 2005/11/02 23:58:16.698
2005/11/03 00:07:13.830
2005/11/03 00:10:14.971
I would like to merge a colum with a date and a column with a time into one to show DD/MM/YYYY HH:MM
I have tried merge cells and it takes out the time and leaves the date only.