24hr Timing issue

**heramiah** · 07-10-2009, 06:59 AM

Hi, i really hope someone can help me with this problem. I'm ok with excel formulas, but damn this problem has been doing my head in for a few days now.

anyway, i have a huge spreadsheet with individuals arrival date and time (A2) and leaving date and time (B2). These date and time entries are in the format: "dd/mm/yyyy hh:mm".

Ultimately i need to show how many are in attendance at any given day and time. I have decided the best way to do this is to have hour slots (columns) and then have a formula to simply insert a "1" in each and every hour slot that the case was present until leaving. this works fine, until the person leaves the next day - i cannot get the formula to handle the 24hr time!.

To give you and idea of the ideal ultimate outcome - what i am eventually wanting to do is plot on a chart the busiest hours. I have thousands of row entries of arrival and leaving times going back a couple of years.

i have attached a simple cut down version of the problem. Any help is appreciated.

**DonkeyOte** · 07-10-2009, 07:16 AM

here's one relatively long winded approach:

Please Login or Register  to view this content.

Apply across matrix

**Bob Phillips** · 07-10-2009, 07:26 AM

Try this in U2

=IF(AND(MOD($A2,1) <=U$1+TIME(1,0,0),U$1<=MOD($B2,1)+(INT($B2)>INT($A2))),1,"")

**DonkeyOte** · 07-10-2009, 07:31 AM

Hi Bob, I think the cell refs in your formula are slightly awry ... also does not account for DateTime stamp values in A/B (ie A/B always > row 1) ... as I see it also ignores issues of crossing midnight but I could be wrong...

**Bob Phillips** · 07-10-2009, 07:44 AM

Originally Posted by DonkeyOte

Hi Bob, I think the cell refs in your formula are slightly awry ... also does not account for DateTime stamp values in A/B (ie A/B always > row 1) ... as I see it also ignores issues of crossing midnight but I could be wrong...

I originally put it in U15, hence the invalid cell references, so I could check my results against the example, and it worked fine, but I made a right mess of transcribing it.

**DonkeyOte** · 07-10-2009, 07:51 AM

Thanks for the update... that said I still don't think the revised version in it's current format captures hours worked in the am when crossing midnight (the AND in present format would never be True in the early hours).

Edit: as I say I think post # 2 works but must be a more succinct approach available...

**daddylonglegs** · 07-10-2009, 07:58 AM

You could use this formula in C2 copied across and down

=IF((MOD($A2,1)-"1:00"<C$1)+(MOD($B2,1)>C$1)+(MOD($B2,1)<MOD($A2,1))=2,1,"")

**heramiah** · 07-10-2009, 09:32 AM

Hi guys, all this is absolutely brilliant. i've been trying to solve this for 2 days and you guys came back to me in minutes! Ive tried all the suggestions and they all work to some extend, but bobs formula does show issues with handline am times. nevertheless; all the help is great. think i'll use this forum more often in future! thanks!