Help needed to summarize data

[CoRN]

Hi guys,

I'm really lost with how to do this...

Basically, my objective is this:
to find out how many overtime hours worked and segregating them into weekdays and weekends.

Overtime hours:
Weekdays: anything beyond 8 hours.
Weekends: all hours counted as overtime.

Thanks!!

-=CoRN=-

[djauncey]

Hi CoRN

The first thing is you'll need to put a column in saying either Weekday or Weekend under each date so the following formula will work

=IF(B2="weekday",((SUMIF(B3:B32,">8",B3:B32))-(COUNTIF(B3:B32,">8")*8)),(SUM(B3:B32)))

I've attached your example with the formula to show it works.

Hope this helps

example.xlsx

[CoRN]

Hi djauncey,

wow!! thanks for the quick follow up... just one more quick question... any quick way to convert or determine the dates if it's a weekday or a weekend?

[djauncey]

Umm there's the =weekday(@@##,#) formula which I would suggest using with option 2 in the return type (after the comma) so it says monday = 1 through to Sunday =7.

So in cell b2

=weekday(b1,2)


Then just change the start of the If formula at the bottom to start with the following

if(B2>5, ...

That should work I think.

Hope this helps

[Pepe Le Mokko]

Hi djauncey,

wow!! thanks for the quick follow up... just one more quick question... any quick way to convert or determine the dates if it's a weekday or a weekend?

New question = new thread !

[djauncey]

Whilst true, it was technically still the same issue as originally stated, just my first response created a followup question.

[Pepe Le Mokko]

Please read forum rules

[CoRN]

Understood!

[CoRN]

Umm there's the =weekday(@@##,#) formula which I would suggest using with option 2 in the return type (after the comma) so it says monday = 1 through to Sunday =7.

So in cell b2

=weekday(b1,2)


Then just change the start of the If formula at the bottom to start with the following

if(B2>5, ...

That should work I think.

Hope this helps

It worked!!! with minor change... should be if(B2<=5)...
again.... thanks djauncey!

[djauncey]

Oh yeah that would do it. Glad I could help in the end