Excel - need help :P
- Thread starter zYx
- Start date
You could mess around with parsing the times and doing calculations based on that, but it would be easier to just split each day column into two and have a 'Start Time' and 'End Time' then it would just be a straightforward sum and subtraction.
i.e.
https://docs.google.com/spreadsheet/ccc?key=0AlAZiY1h-6FpdGxmVkt4NDdubFBUbDEzdk40OE93cWc
i.e.
https://docs.google.com/spreadsheet/ccc?key=0AlAZiY1h-6FpdGxmVkt4NDdubFBUbDEzdk40OE93cWc
Last edited:
Don't know if excel will count it as hours.
You could do it one of two ways...
Firstly only enter in number of hours worked, so monday would be 8 hours (not sure if you're having lunch). Then do a total count for the week.
Secondly a little more complex, but for each day you have two separate columns for start and finish time. But not using time format so 8:00 would be 0800. Then have a hidden column on the side that subtracts the start time from the finish time to calculate the hours. Then count up the hidden columns for the total week.
You could do it one of two ways...
Firstly only enter in number of hours worked, so monday would be 8 hours (not sure if you're having lunch). Then do a total count for the week.
Secondly a little more complex, but for each day you have two separate columns for start and finish time. But not using time format so 8:00 would be 0800. Then have a hidden column on the side that subtracts the start time from the finish time to calculate the hours. Then count up the hidden columns for the total week.
I can do that.
It looks fine, however this is to be printed so I'd prefer for times to be displayed as times, as 8:00. Is this doable?
I'll have a go now.
Associate
- Joined
- 15 Jun 2009
- Posts
- 2,494
This is what I was going to say, Don't even need the hidden column really
What's your formula then
Associate
- Joined
- 15 Jun 2009
- Posts
- 2,494
Interactive version courtesy of Rich_L. Should explain all
Last edited:
This is what I was going to say, Don't even need the hidden column really
Perfect, i wasn't sure if excel would add up that format.
Can you change the format for the total column to just number?
I didn't see this link earlier. cheers, looks good*cough* points up at my post
All I need now is to bypass things like "Off", A/L" etc.
Yeah, I'm investigating it nowInteractive version courtesy of Rich_L. Should explain all
I've tried that by changing the format of that cell, and it would show as 0,854..
Last edited:
Associate
- Joined
- 15 Jun 2009
- Posts
- 2,494
]
https://docs.google.com/spreadsheet/ccc?key=0Aljf3hJZmCQBdC1lcVotZjVWMVVWX0pFWnl4Wi1uMXc
Quite messy, But seems to do the job.
Column H is the new hours, Column G being the old one, Can be hidden but still needs to be there i think.
There is also probably a MUCH easier way to do this but I'm quite simple with Excel.
I know what you're saying
but I think any formula that works in excel is fine.Checking it out now
Not quite working. When time in is 11:30 and time out 17:00, excel shows 5 in H column.
Last edited:
Associate
- Joined
- 15 Jun 2009
- Posts
- 2,494
If you check out the updated one, Turns out there is actually a formula to do all the work for you
For me wasting all that time.
Edit: See the third block down in that google docs example.
For me wasting all that time.Edit: See the third block down in that google docs example.
Last edited:
I got this done.
https://docs.google.com/spreadsheet/ccc?key=0Ahs2KHP-z4M9dFQ2S0x2U1NzTWVYamZ3bE1HcWRrNmc
It works pretty well so far, however I need to be able to type other things in time cells, cantaining text, not always time.
Is there any way to make the formula ignore them cell with other things rather then time?
https://docs.google.com/spreadsheet/ccc?key=0Ahs2KHP-z4M9dFQ2S0x2U1NzTWVYamZ3bE1HcWRrNmc
It works pretty well so far, however I need to be able to type other things in time cells, cantaining text, not always time.
Is there any way to make the formula ignore them cell with other things rather then time?
Soldato
- Joined
- 4 Nov 2006
- Posts
- 2,752
- Location
- Yorkshire
Did this a while ago if it's of any use ... just enter the start/finish time and it will do the rest
Did this a while ago if it's of any use ... just enter the start/finish time and it will do the rest
Have you got the link to excel file?
Soldato
- Joined
- 4 Nov 2006
- Posts
- 2,752
- Location
- Yorkshire
No but I can send it to your via e-mail ... message me via trust and i'll sort you out
I can't PM you here, I think you blocked your PMs.
My email: xxxnns [at] gmail [dot] com (spam ready
)
Soldato
- Joined
- 4 Nov 2006
- Posts
- 2,752
- Location
- Yorkshire
There is no PM's on here, you click trust for the member and click "email member" ... nevermind, i'll email you direct