Adding times together

[david.paton]

Hi,

I have columns for a start time and an end time for all the days of the week and a column and a column that has the difference, which gives the hours worked. I have a cell on the page that has the total hours worked in the week but even if there is hours in the hours worked column, the Total hours worked cell remains blank despite having an autosum for the different hours worked each day in the week.

Can someone help me soon please as I need to have this made for my boss ASAP? Thank you,

Dave

 

[pr2-eugin]

This question is posted under Tables section but sounds like you need Excel help? Is that correct?

 

[namliam]

In this kind of situation, it is very usefull to post your work so far so that people can find out what you did wrong....

 

[jdraw]

You may want to check the DateAdd function here. But as others have said some sample data would help the reader to understand the requirement.

 

[david.paton]

Sorry guys, I forgot this was a Access forum, I will go look for an excel forum.

 

[david.paton]

I think I remember why I posted here. I thought that what I wanted to do would require VBA coding, and this is an Access forum, with people that know a lot about VBA coding so I decided to post on this site.

 

[namliam]

There is an excel part to this forum as well, its not a problem posting here... however my statement still rings true

In this kind of situation, it is very usefull to post your work so far so that people can find out what you did wrong....

 

[RainLover]

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.

 

[namliam]

RL, assuming the data entered into excel is already a "proper" time you can use that to do any calculations you like..... sum will work just as well on "real" times as it does on "decimal" times, which is why its important to see whats going on in his spreadsheet.

May be as simple as applying a format to a cell like :mm:ss or something else

 

[RainLover]

RL, assuming the data entered into excel is already a "proper" time you can use that to do any calculations you like..... sum will work just as well on "real" times as it does on "decimal" times, which is why its important to see whats going on in his spreadsheet.

May be as simple as applying a format to a cell like :mm:ss or something else

The initial result, the difference won't sum correctly for me. Or should I say, won't display the way I would like.

Do you wish to see my sample, or simply wait for the OP to get back to us.

 

[namliam]

Time (by default) is on a 24 hour "clock" if you add together time to a point where you go over the 24 hour limit, you get a day...
Thus if you add together 5 workdays of 8 hours you have a total of 40 hours or 24 + 16 hours or 1 day + 16 hours. those 16 hours you will see (by default), which will be your "wrong" result. As you may or may not know, date and time is nothing but a double value at its core, 8 hours being 0.3333333333 or 1/3 of 24 hours.
The only reason you are seing 8:00 is because of the format on the cell, add the 5 days for a total of 1.666666666 or 40 hours, only reason you are seeing 16:00:00 or perhaps 01/01/00 16:00 or something along those lines, is because of the formatting.

The way to "fix it" is to change your formatting, to NOT use the 24 hour clock but instead show the full number of hours you use [h] in the format (I know I said it was which is the dutch version, sorry). The format of [h]:mm:ss, wont show the 16 hours or whatever "faulty" result, instead will show 40:00:00

 

[RainLover]

Time (by default) is on a 24 hour "clock" if you add together time to a point where you go over the 24 hour limit, you get a day...
Thus if you add together 5 workdays of 8 hours you have a total of 40 hours or 24 + 16 hours or 1 day + 16 hours. those 16 hours you will see (by default), which will be your "wrong" result. As you may or may not know, date and time is nothing but a double value at its core, 8 hours being 0.3333333333 or 1/3 of 24 hours.
The only reason you are seing 8:00 is because of the format on the cell, add the 5 days for a total of 1.666666666 or 40 hours, only reason you are seeing 16:00:00 or perhaps 01/01/00 16:00 or something along those lines, is because of the formatting.

The way to "fix it" is to change your formatting, to NOT use the 24 hour clock but instead show the full number of hours you use [h] in the format (I know I said it was which is the dutch version, sorry). The format of [h]:mm:ss, wont show the 16 hours or whatever "faulty" result, instead will show 40:00:00

I can't get a correct result with formatting. I can only get it to work by converting the Data.

Have you Tested this? If so could I see a copy in 2003.

I have attached my sample.

 

[namliam]

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...

 

[david.paton]

Ok then, here is my work.

This is a bit about what I am trying to achieve.

I am trying to make a timesheet for my boss and I found a formula on the forums that finds the hours worked from the start and the end time, taking half an hour for lunch into account. Here is that formula:
=IF((F5<D5)+F5-D5>8/24,(F5<D5)+F5-D5-0.5/24,(F5<D5)+F5-D5)


That works good but I also have to take into account that I don’t have half an hour for lunch. The time I spend on lunch is subtracted from the total hours worked. This lunch time is recorded in E5 and I also have a column to record hours worked in the evening, which is added to the hours worked. This is in G5.


So I have the hours worked column, J=F-D-E+G, or Hours worked=Finish – Start – Lunch + Evening.


I then have to sum all the hours worked for each day in the 2 week period and put the result in E23, where time in lieu is worked out. This cell has the total hours worked in the fortnight. This part doesn’t seem to be summing the hours worked correctly and I am not sure how to get it to work.


One more thing I couldn’t do, I have the contracted hours in cell F28, which is changed depending on the employee. This is the hours they are required to work. I have it all formatted as HH:MM in the custom section of the format cells. Time in lieu is carried forward. This is added to hours worked, sick & leave hours with the total in cell F27. This has the contracted hours subtracted from it to form any additional time in lieu created which is carried on to the next 2 week period.



I can’t get the contracted hours (F28) field to allow me to enter in numbers that I want to. For instance, I enter 30: into the contracted hours field for someone who works 30 hours and it changes them to 6:00. How do I get the contracted hours field to allow me to enter the correct amount, whilst still being compatible with the other time cells so calculations can be performed on them.


I just discovered another problem. I can’t have time in lieu for the two week period greater then 24 hours. How do I allow that?



I had to waffle on a bit to explain all this so I will give people a concise summary so they know what I want.


1. - I need help with the formula to allow me to have Finish-Start-Lunch+Evening=hours worked

2. - I need help with the hours worked in the fortnight to sum to the total of the hours worked column
3. - I need help with the contracted hours (F28) and how it won’t let you enter the amount you type in, it keeps changing.
4. - I need help with the total time in lieu being greater than 24 in a two week period defaulting back to 0, such as, if it equals 25, the time in lieu would be 1.


That would be greatly appreciated if someone could help me with this as my boss wants it by next week.


Thanks,
Dave

 

[namliam]

I then have to sum all the hours worked for each day in the 2 week period and put the result in E23, where time in lieu is worked out. This cell has the total hours worked in the fortnight. This part doesn’t seem to be summing the hours worked correctly and I am not sure how to get it to work.
...
I can’t get the contracted hours (F28)
...
two week period greater then 24 hours

Formatting issue, just apply a time format to it. Right click the field > Format cells > Custom>[H]:MM

Simply see the earlier attached sample from me to see it "in action"

 

[david.paton]

I already have them formatted as h:mm

 

[namliam]

I already have them formatted as h:mm

Details matter David, [h] is not the same as h

 

[david.paton]

This formula appears to work out the hours worked bit.

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

That is the first thing of the list, now I just need help with the last 3.

Thanks

 

[david.paton]

Details matter David, [h] is not the same as h

But there isn't a [h]:MM only a [H]:MM:SS but I didn't want seconds.

 

[namliam]

If you go into Custom, you can ACTUALLY TYPE YOURSELF whatever (crazy) format you want... No need to pick from any list.