Forum Home
Press F1
Thread ID: 77358	2007-03-07 02:01:00	Excelling in wages with Excel	Brain_Cell_Anon (261)	Press F1

Post ID	Timestamp	Content	User
530803	2007-03-09 22:22:00	Thanks for explaining that for me Mike. Yes Ive broken down normal time and overtime into two separate cells and the sum of these would be the total hours (ie 8:53 + 2:07 = 11:00 in my example). You could of course add another column which sums these two to display the total time. regards, Graham	Parry (5696)
530804	2007-03-11 19:04:00	Bloody typical! Asked to do a shift for a sick mate! Sleep? What's that then? Now, where am I? Mike and Parry Thanks for that! I've had a play and find it works. Er, almost! Unfortunately, only if the DATES are in the same day. As I said, some shifts start in one day but finish in the next. Ieg start 15:00, finish 00:57! If I alter B2 (end date and time is 01/01/2007 10:07:00 p.m.) to 02/01/2007 00:57:00 a.m. sadly C2 now displays 13:47, D2 displays 00:00 and E2 (the sum of C2 + D2) displays 13:47! So close I could crush a grape!! :( I'm thinking that your formulas work for the vast majority of my shifts, so when I actually do a 'cross day' shift, perhaps I should just treat it as an individual item! I thank you for your help (so far!??) and (unless there is copyright involved ;) ) I'll make an 'Offsite Backup Copy' of your formulas please? Many regards, The Cell :D PS I still can't help thinking that (and I know it's long winded) if I do the =RIGHT(B2,5) in a separate cell which changes it to text, but displays the 'time' only, then maybe format it back to time hh:mm, then take that from another cell with 20:00 (strewth, what am I thinking?) I think the answer can be found that way. Then, after some thought, perhaps THAT formula can be 'shrunk' some how!!?!?! Still interested in thoughts, a!	Brain_Cell_Anon (261)
530805	2007-03-11 22:50:00	Hi, this might be better. Change the formula in D2 to this... =IF(DAY(A2)+TIME(HOUR("20:00"),0,0)<DAY(B2)+TIME(HOUR(B2),MINUTE(B2),0),B2-(DAY(B2)+TIMEVALUE("20:00")),0) In cell E2 which has the total number of hours amend the custom format to [H]:MM rather than H:MM as this will allow a count of hours spanning multiple days. hth	Parry (5696)
530806	2007-03-12 15:51:00	Thanks for your patience Parry! Right, first off, I have your original formulas (working for 'same day' times, thanks again!) in new cells further down the sheet. Safe and sound! They seem quite happy with almost any time I 'chuck' at them. I've 'restarted' the spreadsheet and rounded the times (my poor head trying to figure out 'where the mirrors' are!! LOL ) so now it looks like this: A2 = 'Start time' (01/03/2007 15:00) B2 = 'Finish time' (02/03/2007 01:00) C2 = (B2-A2)-TIME(HOUR(D2),MINUTE(D2),0) D2 = IF(DAY(A2)+TIME(HOUR("20:00"),0,0)<DAY(B2)+TIME(H OUR(B2),MINUTE(B2),0),B2-(DAY(B2)+TIMEVALUE("20:00")),0) E2 = C2+D2 I am blown away as C2 now displays 05:00 hours (15:00 to 20:00) and D2 displays also 05:00 hours (20:00 to 01:00)! Brillo pads! But alas E2 (I formated E2 as 'Custom' - '[H]:MM' as you said and I feel awful about this as I'm sure you've worked quite hard on helping me!) displays: 93970:00 Dad! I think I've broken somefink!! A2 to E2 formulas are copied direct from the sheet, so if you can see something I've messed up in my copying and pasting that I can't, feel free to firebomb me!! A2 and B2 are formated as 'Custom' - 'DD/MM/YYYY HH:MM', C2 and D2 are formated as 'Custom' - 'HH:MM'. I should mention that when I first tried to copy and paste your new formula for D2 I didn't realise there was a gap between the H and OUR (as it breaks there on the page). But when it started displaying '#NAME?' I knew I'd done something wrong so went through it and noticed the gap. I hope you have just a little more patience in you!??! Many regards, The Cell :D	Brain_Cell_Anon (261)
530807	2007-03-13 21:45:00	Parry! I think I've answered the 'E2 problem'. You said: In cell E2 which has the total number of hours amend the custom format to [H]:MM rather than H:MM as this will allow a count of hours spanning multiple days. but as I was playing, I reasoned that as E2 did not need to be 'aware' of which day C2 or D2 had come from (or for that matter A2 or B2 either) and basically just needed to display 'Total time', I reformatted the cell as 'Custom' - 'HH:MM' and it now displays '10:00'. Five hours before 20:00 and five hours after (er, as in the very last scenario I gave, start 01/03/2007 15:00 and finish 02/03/2007 01:00)! Your work has paid off! I thank you very much indeed. Many, many regards, The Cell :D	Brain_Cell_Anon (261)
530808	2007-03-14 21:46:00	Hi, sorry for the late reply - I havent been ignoring you just been busy lately. Glad you have it sussed, post again if you have problems with it. regards, Graham	Parry (5696)
530809	2007-03-15 03:27:00	post again if you have problems with it.When I grow up, I'm going to know as much about Excel as Parry :D Mike.	Mike (15)
530810	2007-03-15 15:12:00	Parry Once again thank you! I agree with Mike! :thumbs: Hey Mike? Er, slightly off topic, sorry! What 'clout' have you got with Google, a? Just for fun I Google 'Earthed' your coordinates and see your in (near??) Tauranga! But I can zoom in quite a bit and see cars even! Up here in 'sub-tropical' Whangarei, I can hardly see my suburb, let alone my house! And as for our car.... well forget it! I did find, many moons ago, an aerial photography site that 'came off' the Whangarei local council website. Could even see 'She who MUST be obeyed' cleaning the car (what do you mean I should do that? That's womens work!!!! ;) ). But I can't find it now. It was free even, but now all I can find is pay for sites. Any way, again thanks all! The Cell :D	Brain_Cell_Anon (261)
530811	2007-03-16 03:18:00	What 'clout' have you got with Google, a? Just for fun I Google 'Earthed' your coordinates and see your in (near??) Tauranga! But I can zoom in quite a bit and see cars even! Up here in 'sub-tropical' Whangarei, I can hardly see my suburb, let alone my house! And as for our car.... well forget it! I did find, many moons ago, an aerial photography site that 'came off' the Whangarei local council website. Could even see 'She who MUST be obeyed' cleaning the car (what do you mean I should do that? That's womens work!!!! ;) ). But I can't find it now. It was free even, but now all I can find is pay for sites.I see much more of Tauranga at work than you can see on Google Earth. Of course, if you punch in my coordinates on my computer at work, you'll see my house, rather than just know it's in Tauranga :) I don't have much clout with Google, however... they just get what's made available to them over time. There are some parts of NZ with far better photography than Tauranga. In the US, however, all that stuff is public domain, which is why the quality is so much better than the not-so-great stuff that is made available in NZ. Mike.	Mike (15)
1 2