date and time

Hi, How would you get a vba defined string e.g. "050914" to be ouput in a cell as "05/09/2014" (or just "05/09/14") It must be classed as an actual date, not just a string in the cell for sorting and ordering etc. Thanks in advance.

I have difficulties in date and time format. Since I always need to change the day/month/year. Usually I just did it manually, however after I found this amazing macro things, I hope I can do it using macro which can save me a lot of time. Here the situation is: I have 16 years (1996-2011)...

=IF(M5="","On Service",DATEDIF(K5,L5,"y")&" Y, "&DATEDIF(K5,L5,"ym")& " M, "&DATEDIF(K5,L5,"md")&" D") If Blank It's Returning #NUM!, i dont want that i want Show "On Service" What makes Error here? Or Can make also =If(L5=date(anydate),DATEDIF(K5,L5,"y")&" Y, "&DATEDIF(K5,L5,"ym")& " M...

I have cells which contain date and time (29/02/2012 11:12:40) I need the date to remain and the time to be removed and placed in an adjacent cell Anyone know of a quick method to do this?

I've gotten pretty good with extracting various types of content from cells. But this is a head scratcher. I want convert time values from imported text data, but the time formats are different. I started building a long formula using a series of IF, COUNTIF, MID, FIND, AND, OR, and TIME...

I am in real trouble. I have been searching endlessly for a solution and have gotten nowhere. Basically I have a report that needs to be updated every month on the redemptions a website has orders through. I receive a datasheet that has basically all the data before hand from where it all...

Hello! I found this code here-under in the archives of mrexcel.com and it works great. The code basically records changing values in an excel cell. Upon detecting a change in the specified cell value, this code records the new value in another cell, and basically keeps a log of changes. I wish...

HI, I receive data in the format below. I would like to check how many entries were there between 9 am and 5 pm for the range of date specified. Normally it is for a month(01/03/2014-31/03/2014). Is there a formula that I can use to find the total number of entries that fall within this period...

Hey All, A while back I used a vb I found here to lock cells that have been edited and it works great; I was wondering if there is anyway to add a comment with the date and time of the entry. The code I use is below Private Sub Worksheet_Change(ByVal Target As Range) Dim Answ As String...

Hi All...

Hi there, I have a load of data that I have pulled off an online data source and it has put the time at data in one cell in the format "22.05.2012 17:18:47". The date and time is to be plotted on the x-axis against tunneling distance achieved on the y-axis. Currently when I attempt to plot the...

Hey hey, Is there a way for a date and time to be added into say B1 when any data is added into A1? I could swear I've had this running before, when I've been adding rows of contacts, in order to let me know what dates batches were added. I cannot for the life of me remember/ figure out how...

Hi there, I have a column containing a large-ish number of cells (column "A"). Each cell contains a concatenation of a dollar figure (can be pos or neg) & a card number & a datetime (MM/DD/YY hh:ss). Example: <tbody> -2.23|6202xxxxxxxxxxxx|08/06/13 10:02 </tbody> I have generally been...

Hi, I think there's an easy answer to this one but I can't work it out. I have a range of data where A2 = a date in (dd/mm/yy) and B2 has the time in (hh:mm:ss). What's the best way to take the times from Column B and add them to Column A's data in (dd/mm/yy hh:mm:ss) Thanks in advance, Ian.

I took a date that was in text format like so <tbody> Thu May 23 13:29:43 PDT 2013 </tbody> and I used the Text to Columns function. I then concatenated the month, day, and year, and then added a slash, so the end result of the new data appears as May/23/2013. Unfortunately, Excel does...

Hi I have data showing when staff arrive at, enter after clearing security and then exit a building after completing their task. The data for each cell is a combined date and time as opposed to separate cells for date and time. I am trying to ascertain how long our security staff take to clear...

Hello everyone. I have a pivot table that has two columns that contain dates. I am trying to add a column with a OR formula that returns true or false base on whether the first date is equal to the second date OR the first date +1 equals the second date. The formuala I use is...

Hi, I have designed a form and need to capture a prior date & time using the form. I tried using DTpicker, but I am able to select only date and it takes the current system time. If I change the format to dtptime, I am able to select time but it captures today's date. I should be able to...

<tbody> A B C Here is what i need 1 Date # of hours Start Date____i want to enter any day 2 1/1/12 0 # of hours_____enter any number of hours 3 1/2/12 6 End Date______need a return date 4 1/3/12 6 so if i enter 1/2/12 as enter date 5 1/4/12 6 and 24 hours 6 1/5/12 0 i need the end...

I have a column (T) full of cells with a date and time format <TABLE style="WIDTH: 78pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=104 border=0><COLGROUP><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><TBODY><TR style="HEIGHT: 12.75pt...