Last 365 days only days that I worked

[richblake74]

Hello all and thank you in advance.
I would like my logbook to total the values from a column based on only the last WORK days that I worked.
Then I would also like a cell that totals all the Values based on the last 365 calendar days.

I have included the workbook hopefully that will explain things much better.
Thank you all.

 

[richblake74]

=COUNTIFS(B,"<="&TODAY(),B,">="&,B2,e,"<>"&"OFF") would this do anything to solve my counting?

 

[jasonb75]

Try this in F2 and fill down,

=IF(B2>TODAY(),0,F3+1)

Then this to get your total

=SUMIFS(D:D,F:F,">0",F:F,"<=365")

Format as [h]:mm:ss

 

[richblake74]

Thank you for you reply but, in the formula I only want it to count WORK days. This seems to be counting all days?
=IF(B2>TODAY(),0,F3+1)

 

[richblake74]

Thank you for you reply but, in the formula I only want it to count WORK days. This seems to be counting all days?
=IF(B2>TODAY(),0,F3+1)


Also what I would like, if I have multiple entries for one day it just counts it as 1 instead of counting all occurrences

 

[jasonb75]

Try this one instead

=IF(B2>TODAY(),0,F3+AND(E2="Work",B3>B2))

 

[richblake74]

Thank you but it still is not working for me. Its still counting the 16th Dec Even though I am OFF (which is should not count) also on the 11th December all the 4 occurrences I want to count as One day.
Thank you
COUNTIFS(B:B,"<="&TODAY(),B:B,">="&B4,X:X,"<>"&"OFF"). I had this is Numbers but doesn't seem to be carrying over to Excel (columns were different)

 

[richblake74]

Thank you but it still is not working for me. Its still counting the 16th Dec Even though I am OFF (which is should not count) also on the 11th December all the 4 occurrences I want to count as One day.
Thank you
COUNTIFS(B:B,"<="&TODAY(),B:B,">="&B4,X:X,"<>"&"OFF"). I had this is Numbers but doesn't seem to be carrying over to Excel (columns were different)
View attachment 2104

=COUNTIFS(B:B,"<="&TODAY(),B:B,">="&B613,E:E,"<>"&"OFF")

 

[jasonb75]

The formula that I suggested is working fine for me with some random dates for testing.

=IF(B2>TODAY(),0,F3+AND(E2="Work",B3>B2))

Assumes that the first date to look at is in B2, that the formula is going into F2 and that the Work / Off formula is in column E.

Are your dates proper valid excel dates formatted as dd-mmm-yy or are they text strings that look like dates?
If you change the format of the date cells to 'General' proper dates will change to 5 digit numbers between 48000 and 49000 for the dates in your example. If they don't change then they are invalid text dates which might be the cause of the formulas not working.

 

[richblake74]

That was all that I needed etc, thank you all very much for everything. Works perfect