1904 date system and Pivot tables

[ShayDub]

Hi - first post

I have a spreadsheet and I've calculated times worked for employees. It shows not only how long they worked but also what they worked on. A pivot was ideal to summarise this information but unfortunately when the hours exceed 24 eg 25 the total in the Pivot shows 1. The [h]:mm does not work on the pivot table although it works fine on the data. Is there a solution for the pivot or should I think of a different way of summarising? I used the 1904 system so I could display negative hours.

Thanks (sorry if this has already been posted)

Shay

[JosephP]

can you post a sample workbook? I don't really get how you can have negative hours worked

[Richard Buttrey]

Why are you choosing to use the 1904 date system when your profile says you're using Excel 2010 implying you're using Excel for Windows rather than Excel for Mac.

But in any case a negative number is most probably not be being generated because of the date, but rather that the end time is less than the start time. It's usually necessary to test for this with an IF...test and adjust by the appropriate factor - perhaps 24 in this case?

[ShayDub]

Hi, the reason I'm using the 1904 system is that the 1900 does not handle negative times. The calculation I'm working on is time worked against contract hours so if they did not work their contract hours the answer should be a negative. That part works fine but the Pivot table is not, once the amount of hours exceeds 24 it's a problem. The pivot analyses what the employees were working on. I'm coming to the conclusion that there is no answer to this!

[ShayDub]

Hi, I will post later, thanks for your feedback. The negative hours comes from calculating hours worked minus contract hours, so if hours worked is less than contract hours the answer should be negative.

[Richard Buttrey]

Hi, the reason I'm using the 1904 system is that the 1900 does not handle negative times.

I find this puzzling.
As far as I'm aware the only difference between having Excel set to 1904 rather than 1900 is that the same date serial number produces dates four years apart. I've never heard of there being any calculation difference in the way you say since whatever the base year setting all cells use the same base.

Would you give some specific examples when your system is set to both 1900 & 1904 - probably easier just to upload the workbook and add some manual calculated results tha show what you are trying to achieve from the data given.

[ShayDub]

Hi Richard

I will get round to uploading but I've been playing around with it so much I just need to tidy it up first. In the meantime type 12:00 in A1 and 11:00 in A2. In A3 type =A2-A1 and you will see my problem. Change the date system and you will get -1:00. This is working out for me but the problem is when I generate a Pivot, the Pivot data will not add up hours over 24 even though my main spreadsheet will.

Shay

[Richard Buttrey]

Hi,

I don't see any problem.

Your A2 time number (0.4583) is smaller than your A1 time number (0.5). Deducting A1 from A2 gives you a negative number. And that's the case whether the system is in 1900 or 1904 base year mode because time numbers are independent of dates. Hence you see in this example the number -0.0416667 in A3.

The real question is what result do you WANT to see when an earlier time is deducted from a later time?

[ShayDub]

Hi Richard

Maybe I'm missing something here. The answer I want in the above example is -1:00. Format your cells for time hh:mm and you will not get this result unless you change the date system. If I'm wrong I will be delighted! I'm just looking for a solution.

A clarification, I notice that the date system sorts out the negative times but the Pivot table doesn't work either way. On the pivot table, the total row would show 00:00 if it was adding 12:00 and 12:00. The answer I need to show is 24:00. My main spreadsheet will do this but the pivot table will not. I am coming to the conclusion that I will have to summarise a different way. I will post later, thanks.

Shay

[ShayDub]

OK, I think I've identified my issue. The date system is causing the problem afterall. On the spreadsheet attached you can add hours to the data in c2:c13 and when you refresh the Pivot it works the way I want it. However, if you change the date system the Pivot no longer works. This is because the times listed in ColB now contain dates as well as times. There is my problem! I need to use the 1904 system for calculating negative times as I explained previously.

Shay

[Richard Buttrey]

Hi,

I still don't understand.

You say you are wanting to show negative times implying that you're deducting one time from another, but nowhere in your data do I see any calculations which you could use.

Neither do I see why you actually need a Pivot Table since all it's doing at the moment is simply replicating your data.

When you say the answer you want is -1, where do you expect to see that and what values are used to calculate this? It's not obvious, at least to me, where you get -1 from. Perhaps manually sketch out what a Pivot Table should look like and mark the cells which should give you -1. It would also help if you described the overall aim of the task since there may well be other / better ways of achieving it.

For sure your problem is nothing to do with the date system. All that happens if you set it to 1904 is that you see dates in 2013. If you set it to the 1900 base then you see 2009 dates. (Assuming they are supposed to be 2013 then the system was originally put together when the system WAS set to 2013, perhaps using Excel for Mac which defaults to the 1904 base).

As I said before Times are independent of the date base. Necessarily so because when they are present as part of a date/time number they only use the decimal part of the number, and of course if they are only created as time numbers they are automatically only decimal numbers.

[ShayDub]

Hi Richard

Thanks again for your time. Apologies for the sample I uploaded, it does not show the negative calculation, I only posted to show the problem I was having with the Pivot.

Each employee is contracted to work x number of hours. If they work more than their contract it will calculate how many hours they exceeded their hours. If they did not work up their contract hours I have a calculation to show by how many hours they were short. This is where the negative value is shown. Maybe I could have gone about that a different way instead of showing a negative value but that's the way I did it. For example, contract hours = 20 hours, hours worked 25 therefore 5 hrs over, contract hours = 20 hours, hours worked 15 therefore -5 hours. The timesheets cover each week and the full year so I have a breakdown of weekly hours and yearly.

Anyway, whether I went about it the right way or not, the point is that I had to change the date system to show negative times - that's a fact. I got around the problem with the pivot table. I did a paste link of my original data to a new spreadsheet with the 1900 date system, adjusted the dates and now the Pivot table is working fine, formatted with [H]:mm. So now I have a timesheet for recording times worked and I have a separate spreadsheet analyising the data over the year and it seems to be working well. If I'm still confusing you I will post you a copy of what I did but I've being toying with this for a while I have some crazy formulae in there - it might truly confuse you!

Thanks again

Shay