CODEWITHSUNDEEP
excelexcel-formula
Anuj
Anuj

Reputation: 178

DATE showing in TIME format cell

I have this strange problem, my Column D has time in it and I have set its format as TIME 13:00:00

It displays exact way I want to, but in formula bar it also displays the Date from Column A

My format is:

Date      | Time   | Table | Checkin Time 
6/1/2018   16:21:55  55       16:21:55

When I click on values under Checkin Time, it shows Date + Time

screenshot

I also tried copying and pasting as plain text but got the same result.

Upvotes: 0

Views: 839

Answers (1)

girlvsdata
girlvsdata

Reputation: 1644

From the comments, it looks like you are trying to answer two questions:

  1. How do I remove the "Date" from the "Datetime" so I only see "Time" in the formula bar?
  2. How do I calculate the difference between two times?

I'll answer question 2 first, because then you might realise you don't need the answer to question 1:

Question 2: How do I calculate the difference between two times?

As you can see in the screenshot below, I used this formula:

=TEXT(B3-C3,"h:mm")

Time Difference

Also notice, B3 is formatted as a "Datetime" and C3 is formatted as a "Time," and we still get the correct result. For this reason you probably don't need the answer to Question 1 above, because you don't need to remove the "Date" part out of a "Datetime" to calculate the difference.

There is more information here on different ways to format your output.

Question 1: How do I remove the "Date" from the "Datetime" so I only see "Time" in the formula bar?

As @Jeeped kindly pointed out in the comments:

The time is the decimal portion of the raw underlying value2. You can use =MOD(D2, 1) to extract it. Remember to add 1 to column D's value if column D is less than column B

Upvotes: 1

Related Questions