1

I love the transpose date function above. Can you do the same thing with time in hour increments? If someone checks in at 2pm and checks out at 7pm, the cells to the right would count 2pm, 3pm, 4pm, 5pm, 6pm, 7pm, etc.? That would be awesome.

4
  • Welcome. "I love the transpose date function above". Your data is insufficient. Please edit your question. Also read how to and share a test sheet so as you can be easier helped. Jan 14, 2021 at 17:35
  • @markamitsos, maybe the poster is referring to the DATEDIF function?
    – Erik Tyler
    Jan 15, 2021 at 3:20
  • 1
    Who knows @ErikTyler. Not much to say about a user who posts a question and does not even bother to reply to those who try to help. :( Jan 15, 2021 at 18:09
  • This question has been bumped by Community User 9 times. Oct 15, 2023 at 21:59

1 Answer 1

0

It's unclear what "I love the transpose date function above" means. But given the context, I'm guessing that you mean DATEDIF. If that is correct, there is no equivalent for time. However, you can "trick" DATEDIF into working with time by multiplying each time by 24 (hours in a day). For instance, if check-in time were in A2 and check-out time were in B2:

=DATEDIF(A2*24,B2*24,"D")

If your check-out time will ever go past midnight, you will need to account for that with an IF statement that chooses how to use DATEDIF:

=IF(B2>=A2,DATEDIF(A2*24,B2*24,"D"),24-DATEDIF(B2*24,A2*24,"D"))

Understand that, per the post, this will only work in whole-hour increments without rounding. If you need partial-hour increments, you need to state that in the post. (It would simply require multiplying by 240 or 2400 and then dividing the result by 10 or 100 respectively in the end.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.