datevalue

  1. E

    How to change date by 5 years with a formula?

    Hello, I'm trying to make an excel sheet that tracks my bill payment dates. I need a formula that adds 5 years to the date 27/11/2012 and if that's < today() then add another 5 years and if that's < today() etc. etc. Right now I have 10 IFs just manually adding 5 years to each nested IF...
  2. W

    DATEVALUE function

    I have a column of data which I thought were dates but the data doesn't seem to behave as such. I have attempted to use Datavalue function but no am getting #value ! I believe I have checked my system date and time settings, Doesn't anyone have any advice?
  3. P

    Datevalue

    I have big data in column A of (011 12345 01/01/2019). Then I want columb B to get the dates only. I used the datevalue formula (=datevalue(right(a1;10)).It works but not on several cells with #value !. Data on column A are the same even on cells which has #value !. How can I fix those #value !?
  4. D

    Date in Cell with Text

    Hello - I'm trying to calculate headers for a workbook, where we have a new header by date. The headers would be named "Key Accomplishments: 11/25/18"... "Key Accomplishments: 12/09/18", etc. Right now my formula is: =LEFT($O$1,20) &" " & RIGHT(O1,10)+14 which returns: Key Accomplishments...
  5. B

    DATEVALUE in Excel, handy tips?

    Hi i need to group dates in Excel based on a unnatural selection, namely Saturday to Thursday, so i need an alternative for WEEKNR, could you please help? - i need an unique value like WEEKNR for the dates for each period, for example ("03-11-2018 - 08-11-2018" for the dates within this group)...
  6. N

    Sumproduct Datevalue with Numbers

    For sanity checks, I wish to sumproduct the datevalue of a column with dates with a column of numbers in a table. But I am getting a value error. =SUMPRODUCT(--(DATEVALUE(bank_trans[DATE]))*--(VALUE(bank_trans[PAID OUT]))) How could I do this please? Many thanks, Andrew
  7. V

    Need technical understanding on Formula

    Pls teach me how this formula works.. DateValue and TimeValue
  8. B

    Macro to limit use of a file

    Hi, Is there a macro that will allow me to put some sort of a trial period of the files that I am creating? I have a very slight idea on datevalue but I am not sure if that will work. Basically, I just need a macro that will disable the file once a specific date/time is reached.
  9. T

    Excel not recognizing date in a csv imported date

    Hello, thank you for reading. I'm working with dates that are exported from Kibana like this: <tbody> February 9th 2018, 18:00:00.000 I have tried using =LEFT(A1,FIND(",",A1)-1)) and various combinations of the TEXT and DATEVALUE functions, and have not been able to get the value...
  10. S

    Count ifs condition don't retrieve same result from sharepoint?

    Hello mrexcel community! this is my first post, so I'll just start with my question: I have a sharepoint with the list of tickets that had been already closed. I have connected the sharepoint db (exported to excel) to excel. On my excel worksheet I'm trying to get all the tickets that are...
  11. J

    Timestamp to date - Excel 2010

    Hi All, This is driving me nuts. I have a report which I have exported from a webpage into excel format. I want to equate a timestamp (10/15/2015 8:36:16 AM) to a Work Week number. Would usually do this by =WEEKNUM(A1,2). However getting the #VALUE error. So figured the timestamp must be in...
  12. L

    how to convert string into readable format for =DATEVALUE?

    Hi all, please view image to understand what i'm talking about! I imported billing dates into my master file (column B), using logic in excel I broke up the string of the billing period to properly identify the Start and End dates. Now I would like to convert these strings in columns D and...
  13. P

    what am i doing wrong???

    Hi, i cant figure out what i am doing wrong. Trying to look at a range and test if date value in cell is greater than date value in another cell, if so then enter text in another cell, then go to the next cell and perform the same test, do this till the last row of data in that column. But when...
  14. P

    Insert formula into first and second blank columns, then autofill down

    I import a set of data with dynamic rows and columns. The rows could go up to say 100,000 and the columns, no more than 50. There are headers in row 1. There's a timestamp in Column that is a a string, so I need to get the DATEVALUE and TIMEVALUE from it and insert those values into the first...
  15. P

    unable to convert text to date format using date value

    Hi All I have 2 questions related to dates. 1: Im trying to convert the text Aug 07 2014 08:57 PM into a date format recognized by excel. I tried using the date value function without any success 2: Once I am able to get the date in the right format I need to calculate the time difference ...
  16. J

    VBA Private Sub - How to use DATEVALUE in a VLOOKUP

    I am trying to get a DATEVALUE from another worksheet by using Vlookup. The reason for this is the worksheet I am trying to Vlookup from is not storing the dates as dates and I am unable to simply change the data into a date format as it is 'controlled' by someone else. Currently, the...
  17. A

    Count number of times a word appears during a date range

    I had the formula and things were working great until I saved over it all and now I have tried, unsuccessfully, to find the formula I used online again. I need help! I have a list of grants with different names. I need to count the number of times they appear between 9/1/2012 and 12/31/2012 in...
  18. A

    fill in Date and time where missing on 3 minute interval.

    Hi I am having trouble getting a VBA code to do the following: 2012/05/01 00:00:00 2012/05/01 00:03:00 2012/05/01 00:06:00 2012/05/01 00:15:00 2012/05/01 00:18:00 From this above to this below 2012/05/01 00:00:00 2012/05/01 00:03:00 2012/05/01 00:06:00 2012/05/01 00:09:00 2012/05/01 00:12:00...
  19. E

    convert datevalue to date?

    Can anyone help me to how to convert datevalue to date? I have 41062 as datevalue, and I want to convert it to date as 02-06-2012. when I use concatenate(day(A1);"-";month(A1);"-";year(A1))-> I got it as 2-6-2012. But I want 02-06-2012. How to get that? Pl help
  20. T

    Find and Make Active

    Hi, i have found this code here which i am trying to adjust to my needs Sub finddate() Dim cl As Range Dim lastrow As Long lastrow = Worksheets("Filter_CSM").Range("A65536").End(xlUp).Row for each cl in Range("h2:H" & lastrow).Find(What:=DateValue(<>"")) If Not cl Is Nothing Then cl.Select...

Some videos you may like

This Week's Hot Topics

Top