Any excel expert here?
I've got this question (Pic related)
-The crew for each concert must be 5
-Add 1 for each 500 people ( Capacity )
-Add 2 if the concert stared before 20:00
Now I know that for the second is 8
But it needs a formula which I tried to mess around but didn't get it.
Any idea /g/?
Don't know how to extract the time from a time and date cell off the top of my head, but the crew column would probably be something like this
=IF(TIME >= 20:00, 5 + FLOOR(CAPACITY/500), 5 + FLOOR(CAPACITY/500) + 2)
>>53602645
>writing the same thing twice=5 + FLOOR(CAPACITY/500) + IF(TIME >= 20:00, 0, 2)
=QUOTIENT(E9,500)+5+IF(HOUR(C9)<20,2,0)
Okay it's not really working I'll provide the link to it in a sec
>>53602722
sorry,
E9 should be G3, your capacity column
C9 should be F3, your "concert" column
=QUOTIENT(G3,500)+5+IF(HOUR(F9)<20,2,0)
quotient will return 0 for <500, 1 for >500, 2 for >1000, etc
>>53602645
>>53602658
>>53602722
>>53602727
Okay here I may get fucked but who gives a shit
speedy dot sh/ CdNNg/ Excel dot xlsx
just remove the spaces
>>53602785
http://www.excel-easy.com/examples/date-time-formats.html
you have to change the format of your first column so excel can recognize it and extract the hour. that or make separate columns for date and time
>>53602867
Yep got it.
Thanks alot man!