Removing Timestamp from Date

Follow
Jacob Cluff

I'm trying to subtract two different dates but noticed I'm getting invalid data because of the timestamp. Is there a way to remove the timestamp so I can find the number of days between two dates?

Thanks!

0

Comments

7 comments

Date Votes
  • Pablo Redondo

    Use the roundtime function to round your timestamp to date: ROUNDTIME(#timeEnglishFormat;"1d")

    0
    Comment actions Permalink
  • Jacob Cluff

    That didn't seem to do it. I'm still getting (Jan 1, 2013 12:00:00 AM) and when I subtract from Jan. 11 2013, I'm getting 864,000,000. Thoughts?

    0
    Comment actions Permalink
  • Sujay Mahajan

    Hi Jake - that seems to be in milliseconds. If you divide by 1d - as in "Subtract(#xdate, #ydate)/1d", you should get the number of days. (Or "Subtract(#xdate, #ydate)/86,400,000) - 86,400,000 being number of milliseconds in a day.

    1
    Comment actions Permalink
  • Jacob Cluff

    That's what I was missing... Thanks!

    0
    Comment actions Permalink
  • Pablo Redondo

    oh.. I see. What you are looking at id the difference in miliseconds. In other words, substracting two timestamps or dates by default will produce the result in miliseconds. So to convert it to days you want to divide it by "1d". So the formula will look like #yourresult/1d.. You can type this formula directly in the formula bar. You can also do #yourresult/1m to see in minutes and so on. Let me know if this makes sense. More than happy to provide a screenshot

    0
    Comment actions Permalink
  • Ishfaq

    What if the date is blank for some records, it shows an error in those records when we use Roundtime function

    0
    Comment actions Permalink
  • Alan

    Hi Ishfaq,

    If the values are blank, you can create a new column using the IF function to remove the blank values.

    If the values are null, you can use the DENULLIFY function to remove the nulls.

    Let me know if you have any questions!

    0
    Comment actions Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post