Time and Dates

[excellecxe4ever]

Hi Everyone,

I had a quick question in regards to time and dates in excel.

For example a Date of 5/5/2012 and Time 1:00:00 PM might be 41000 seconds but 5/6/2012 and Time 1:00:00 PM is 41001 seconds.

How can I write a formula that does not take into account the day when converting to seconds?

Does this have anything to do with Julian Dates or am I on the wrong track?

Thanks in Advance.

[jeffreybrown]

I don't quite follow. 1 day equals 86400 seconds. Where do you get 41000?

The numerical value:

5/5/2012 1:00 PM is 41034.5416666667

and

5/6/2012 1:00 PM is 41035.5416666667

[excellecxe4ever]

Hi,

Sorry wrote down the wrong numbers.

The numerical value:

5/5/2012 1:00 PM is 41034.5416666667

and

5/6/2012 1:00 PM is 41035.5416666667

So in regards to the above, why does the 4 change to a 5 for the two different dates?

I have a column with a date and time, is there a way to remove the date automatically?

So that 1:00 PM is the same in seconds for everyday (0.5416666666)?

[jeffreybrown]

To get the right side of the decimal

=MOD(A1,1)

to return the left side of date/time

=INT(A1)

Excel sees time as a decimal with 1 day equal to 1

Excel starts time from 1/1/1900 which is 1 numerically and today is 41082

http://www.cpearson.com/Excel/datetime.htm

[excellecxe4ever]

Now it all makes sense.

Thank you for the help.

[jeffreybrown]

You're welcome and glad to have helped. Thanks for the feedback

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save