Calculating Rostered Hours

If you upload an Excel example workbook, we could write a macro, but I'd strongly recommend getting the source right (i.e. find out and address why it's not outputting correctly) - I'm not being idle, if there's a fundamental problem with your source using a macro to get around it will address one problem but might disguise others...

CC

I won't comment on your Crystal Reports nor completely solve your problem, however, I will show you how to count your hours using built-in FUNCTIONS. Try using:

=IF(CELL("format",A2)="D1",24*(D2-C2),24*(C2-B2)+E1)

Now, you have to get the cell references right, I assumed sth like the picture (formula goes in column E).

Then, to do is grab final value of each date and (if you want) relabel 0 as day-off .

If you manage to do that w/o macro, it'd be sleek!

(i would attempt to reverse the formula, starting from bottom up, but that's me)

Did you get it working yet? Anyhow, if you REALLY want a sleek solution w/o using macros, try instead

=IF(CELL("format",A11)="D1",(D11-C11),C11-B11)+IF(CELL("format",A12)="D1",0,E12)

That will do the trick. Then, use autofilter and filter out all rows with dates in the first column. You will now have what you were asking for!

Originally Posted by zeke varg

Did you get it working yet? Anyhow, if you REALLY want a sleek solution w/o using macros, try instead

=IF(CELL("format",A11)="D1",(D11-C11),C11-B11)+IF(CELL("format",A12)="D1",0,E12)

That will do the trick. Then, use autofilter and filter out all rows with dates in the first column. You will now have what you were asking for!

OKay going to give it a try I don't instantly understand what you are doing by reading it.

I had started down a totally different path. Basically using if tests,

if 1st row has a date record date
then start calculation
if 1st cell has time perform calc
else cell = blank then "day off"
if end time < 2pm then then row -1
perform time calc
elseif perform calculation
end

If i use the =IF(CELL("format",A11 test. How do I make the check if format = date format

I updated it to match the full worksheet I have so IF(D4("dd/mm/yyyy"") but that isn't working for me.

Is there an if date/time test like the isnumber test?

Originally Posted by DonkeyOte

As already mentioned by CC....

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

A sample file speaks a dozen posts... people are generally disinclined to spend their free time recreating a file based on images when you could just post the file here in the first instance...

on an aside I doubt you need / want to use CELL function (it's Volatile)

Advice taken, added example workbook.

Originally Posted by flebber

Advice taken, added example workbook.

:D:D:D

OK, as I mentioned in my first post, it would be better if you could address this by adjusting the source (Clearly, from the example (thanks), it's each new date that adds a value, bumping the lines over) - so I imagine a setting to "print the date for every row" or something like that woudl remove the alginment problem. Again, as stated before, it's better to do this in source as a macro (easily enough written) to fix the alignment might create or mask other problems. It can be clearly seen that the first data row (row 3) would not work with an alignment fix.

Before we write a macro to fix the alignment, can you please see if you can adjust the source data? I hope I've explained why this is preferable.

If the data is fixed, we can use formulae or a macro to create your specified view - are you comfortable with VBA?

CC

I have been toying with crystal reports to get a different output format, the first was a CSV export. This is a bit cleaner output from crystal.

attached