Excel 2007 : How can I find the number of "Nights" between 2 date?

**jopy** · 12-08-2009, 02:30 AM

In cell A1 i had put a date with time (12/1/2009 11:55:00 AM) and in cell B1 another date (12/8/2009 11:55:00 AM). What i need is - in cell C1- total number of night between these dates.. Please help

**teylyn** · 12-08-2009, 04:30 AM

Hi Jopi,

welcome to this forum.

Reading your question, I wonder...., what is your definition of "night"? Can you express in words what you would want Excel to look for and count?

When does "night" start? 7pm? 10pm? When does "night" end. 5am?

If your end date is 12/8/2009 05:55:00 AM, is that still within the night? Has the night passed? should it be counted?

**DonkeyOte** · 12-08-2009, 05:25 AM

If on the off chance you mean in terms of "stay" (hotel) - then perhaps: =INT(B1)-INT(A1)

**darkyam** · 12-08-2009, 09:30 AM

DO, that kind of depends on the hotel's rules. Over here, at least, if you don't check out by a certain time (often 11am or noon), they charge you for another night. I think yours would have to be modified to ROUND(B1,0)-INT(A1) if noon checkout time or ROUND(B1+1/24,0)-INT(A1) if 11.