MS Excel / VBA help thread

Real life LOL

I have a log of timesheet hours and want a chart of one specific activity as a % of total available hours, per 4-day period.
I can do a pivot of the activity hours grouped per 4 days (only works as a non-datamodel pivot for some reason) but can’t work out how to separate one activity from the others (or the total), without adding an additional column to the log which I’m reluctant to do (although it could be a lookup tbf).

Im guessing it would need to be a calculated field that did sum of thing you want to isolate.
If you can't group by the 4 day are all the 4 day periods entered the same way (excel loves a date fuck up).

All the dates are single dates, maybe 50 lines in the log per date - I guess one way would be to copy and paste from two pivot tables into a normal sheet (not sure mirroring will work as the pivots dynamically resize) but I'd like it to be minimal effort as part of a suite of indicators.

I've just figured out measures and how to stack them in the datamodel, last time I played with calculated fields it was a nightmare of slightly different syntax to normal formulas!

If I could get dynamic ranges from 2 pivots into one chart, that would also work but I don't know how to get there.

If this is annoying, apologies in advance:

Where is the data you're pivoting off?
In another sheet in the book?
How many fields?

Date stamp
Item label
Row Id?

Have you made the data a table and not a range? This usually makes things easier as then the pivot will update when the table updates etc etc

You can change pivots to not resize on update, it's in the options when you right click.

No not annoying! I’ll take some pictures and update.

Table (not a range) attached below.

1 Attachment

total available hours

Is that a standard amount? e.g. 8hrs a day

4-day period

is that a sliding window, or does each table just have 4 days in it? How to determine the start/end of each period

I can't think of an obvious way to do that in a pivot as it stands. You could do some godawful things with sumifs and unique and some other shit but it wouldn't be good. A helper column which groups the dates might be the easiest option.

I think your issue with pivots is I don't think you can calculate by the specific contents of a field with a calculated field and you can't use a calculated item when it is grouped.

Total hours is whatever has been logged (team varies from 0-8 people and they're all on slightly different hours).

4 day period needs to align with another team's shift pattern (4 on, 4 off).

Echoing duncs, is it a window or is it a fixed 4 day period? If it's fixed (like week number for eg) that's much easier and a helper to look up and assign this would be easier.

Fixed four day period(s) but I've solved with a boring helper column to determine waiting/working (and standard pivot).

Edit: Not currently enough data logged to show any more bars in the graph.

3 Attachments

You say boring helper, but you could spend hours writing something to “learn how to be better” or you could “do the thing that works and realise you are being better by seeing that adding a helper column is a normal part of etl stuff and then you can put that on your cv and appRaisal”

I have 5 points in a process which have a time and date that each will be completed at. I'd like a final cell which is an 'order open' cell to highlight anything that still open.

I'd be relying on just having all the points being filled in to trigger the change to order closed.

What do I Google for this?

=IF(COUNTA(A1:A5)<5,"Open", "Closed")

If the number of non-empty cells in range A1:A5 is less than five, then show Open, else show Closed.

Something like that? You could customise it depending on how many points you have by replacing "5" with a reference to another cell.

if or statements

https://exceljet.net/formula/if-cell-is-blank

I'm going to guess it's wide and not long data.
Each row is for thing that has 5 points with a final column "closed/open" based on the contents of cells 1:5

https://exceljet.net/excel-functions/excel-if-function

=IF(OR(A1="red",A2="blue"),1,0)

there's also a countblank

=IF(COUNTBLANK(B1:B5)=0,"Closed","Open")

Ah nice, that's a new one for me.

In the spirit of as many solutions as possible

=IF(COUNTIF(C9:G9,"")=0,"Closed","Open")

Whats the string for non blank?
It’s not “”.

In the spirit of as many solutions as possible

=IF(COUNTIF(C9:G9,"")+123<>123,CONCAT("O","p","e","n"),LEFT("ClosedY'all",6))

It’s boring at work today eh?

I think my last one might qualify as production code for some of our stuff at work