1. P


    Hello, I am trying to convert a date with a timestamp into a date value. However, although I tried multiple approaches, like using INT(A2), the value is still not recognized as a date value. And the weird part is that some of the original values are working as a date value when I copy/paste to...
  2. D

    Unable to convert date+time (yy/mm/dd hh:mm) format to excel date

    I have the following column with dates, that I need to convert for excel to recognize them as dates: 21/02/12 15:34 21/02/15 21:18 21/02/17 22:06 21/02/15 21:37 21/02/15 16:05 21/02/17 20:53 21/02/17 00:55 21/02/17 02:34 21/02/19 05:31 I have tried using DATEVALUE, TEXT...
  3. A

    Application.OnTime Datevalue

    found a post, Application.ontime DateValue ???, that showed how to set a specif day of the week for an application.OnTime to run, but cannot get it to work for my file. I want my macro to run every Friday @ 9:00AM but nothing happened when I changed the values to today in order to test. Can...
  4. 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...
  5. Welsh Mark3

    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?
  6. P


    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 !?
  7. 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...
  8. 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)...
  9. 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
  10. V

    Need technical understanding on Formula

    Pls teach me how this formula works.. DateValue and TimeValue
  11. 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.
  12. 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...
  13. 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...
  14. 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...
  15. 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...
  16. 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...
  17. 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...
  18. 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 ...
  19. 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...
  20. 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...

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back