Adding times together

[namliam]

While looking at your sheet(s) I dont understand why you need all those sheets, just have one sheet. People fill it, save it and create a new one for the next 2 week period?

 

[david.paton]

I don't know, I think they want to record all the times worked but I don't know for what reason. I am only doing what I was asked.

 

[david.paton]

I tried to type in that format [h]:mm into E23, the hours worked in the fortnight but it gave me a huge number, 1368:00 which I thought couldn't be right.

 

[RainLover]

Tried, tested and worked with it a million times... I consider myself somewhat of an expert on dates/times considering the amount of problems that people seem to have with them.

Please see your (extended) file attached, Notice how I only changed the formattting on rows 26,27,28 and 30-37.
Where 30 thru 34 is kinda screwing up the information...

You have successfully formatted two different cells to display an acceptable result.

However there is no ability to add those two results and get a usable format. Unless you reformat every cell.

In my sample I have converted from Time Format to a Number format.

 

[david.paton]

You need to convert your answer, End - Start, to a Number rather than Time.

Use =HOUR(H27) to convert the contents of Cell H27. Do the same for the other cells and then you can sum the individual results.

Tested.

But H27 doesn't have any figures in it.

 

[RainLover]

H27 is only used as a sample.

Look at my sheet for a better example

 

[david.paton]

That hour(h27) is does the same thing as that formula I found, right?

This formula entered in the total hours worked column

=(F5-D5+(F5<D5)-E5+G5)*24

 

[RainLover]

I am not sure what you just said.

Give me an hour or so and I will fix the formula on your SS.

I am going to have some food.

 

[namliam]

You have successfully formatted two different cells to display an acceptable result.

My goal was to show what you can do with formatting the same information differently, not to only show what is expected....

However there is no ability to add those two results and get a usable format. Unless you reformat every cell.

Yes well, either you have to use a formula or a format... depends on if you prefer 21.50 or 21:30 as a displayed value

=(F5-D5+(F5<D5)-E5+G5)*24

I tried to type in that format [h]:mm into E23, the hours worked in the fortnight but it gave me a huge number, 1368:00 which I thought couldn't be right.

Sorry but did you read (all) my posts?
If you *24 your times then you get Decimal times, exactly the same as you would do Hour(), except hour will cut off anything less than an hour...
I.e. Hour(7:30) will return 7
7:30 * 24 returns 7.5

Either apply [h]:mm or * 24 or use the Hour and Minute functions to "fix it" dont mix the different solutions because you will create a mess.

 

[david.paton]

Thanks, that would be great, but I am going to bed soon and tomorrow I am going away for a few days and won't be back until sometime sunday and will need to know if I can do it by monday so I am not sure if I will be able to have a conversation on here again before I need to tell my boss if I can do it, so if you could also fix it so it solved those 4 issues I put at the bottom of my really long post (I think I solved one of them but unsure if it will be compatible with results of other fields), I would really appreciate it.

The list was a checklist of 4 items.

Thankyou

 

[namliam]

I believe something along the lines of attached is what you are looking for?

 

[david.paton]

Almost, I need it now to show negative time in lieu if extra hours need to be worked. I think I got that right. Basically, that is great, I just need the time in lieu to allow negative numbers instead of ######### if the number is meant to be negative.

Put some dummy data in to see what I mean such as a large contracted hours eg 80:

 

[RainLover]

Mailman

You can go to bed now. The OP has run out of puff.

Thanks for showing me something different. It would not be the way I would do it, but that is simply a matter of personal choice.

I won't confuse the OP by showing a different version.

Vang je op het volgende onderwerp.

Cheers.

 

[namliam]

Time formats sadly do not support negative values by default

If you have a requirement to display negative values, you will need do one of the following:
1) to resort to decimal hours, with all the drawbacks assiociated with it.

2) resort to some "trickery" to make it look like time using the hour/minute formula's
Something like:
=IF(F27-F28< 0, "-", "") & HOUR(ABS(F27-F28)) & ":" & MINUTE(ABS(F27-F28))
using this only as a display and keeping the "real" time values invisible on the sheet so you can link the sheets.

3) (probably the best)
Or change your excel sheet to work off 1904 as the start year instead of 1900 (which is the default)
Check this: http://support.microsoft.com/kb/182247
To find out how to do it.

 

[RainLover]

Time formats sadly do not support negative values by default

The following applies to Access not Excel.

Days can be negative but times are always positive.

Hence

-3.5 is actually two and a half days before day 0.00

Namely 27 December 12 Noon 1899

30 December 1899 is day zero.

 

[namliam]

Well accoording to excel day 0 is 0/jan/1900, aka 31/Dec/1899...
However assuming you have 1900 as your default year, you cannot display 30/dec/1899 as a date, instead you will see ###################
The negative value is there and you can still use it to calculate your way forward, it just looks bad.

If you have 1904 as your start year, oddly enough 0 becomes 1/Jan/1904... you may expect -1 to then be 31/dec/1903, but thats not true, it is actually -2/jan/1904

not sure if things work differently "down under" but thats the way my Excel works on US settings.

 

[RainLover]

@ namliam

Thanks for that.

I was thinking Access not Excel.

I made a notation to my previous post to explain.

 

[namliam]

Yes, it is wonderfull how simular, yet slightly different Excel and Access are :/

BTW, Might be nice if someone with the power to do so moves this thread to the Excel part of the forum.

 

[pbaldy]

FYI, thread moved to Excel forum.

 

[david.paton]

Not sure what has happened. Just got home after going away for the weekend and I tried it again and it seemed to work. The negatives were displaying as numbers, not ++++++++++++. I think changing it to the 1904 date system did the trick.

I just tested all the columns to check they were adding up and working correctly and I encountered 2 more problems.

I need the Time in Lieu Carried Forward from the previous sheet to equal the Time in Lieu Brought Forward from the current sheet. I just put "='6 Jan'!F29" in cell '20 Jan!F21' but as you can see, '20 Jan!F21' equals -4:00 but '6 Jan!F29' equals -100:00.

Second problem is that where I have to add up all of the Hours worked in fortnight, total sick hours, total annual leave and total special leave, they all seem to add up except the total special leave and I don't know why it won't sum with all the entered hours as it seems exactly the same as the rest.

Have a look at the uploaded spreadsheet to see what I am talking about.

Thanks.