keggerz Posted January 5, 2009 Share Posted January 5, 2009 (edited) Ok so I am making a schedule in Excel and would like to be able to use =Sum function but I can't (or dont know how) because of how the schedule has to be made: Here is what I mean...in one cell I have a shift entered like this: 6:45a-3:45p in another 6:45a-11:30p is there a way that I can get excel to calculate that the 6:45a-3:45p is an 8 hour shift(yeah I need to take out the hour lunch) and to also get it to calculate the 6:45a-11:30p as a 4.75 hour shift? If that can't be done is there a way for me to assign 8 hours to the 6:45a-3:45p cells and so on so that it can calculate the weekly totals for each person as well as the daily totals? Edited January 5, 2009 by keggerz Quote Link to comment Share on other sites More sharing options...
Big Country Posted January 5, 2009 Share Posted January 5, 2009 (edited) Enter the start and end times in different cells (For simplicity sake, say A1 and B1) In cell C1, enter the formula B1-A1. Then, you can sum those calculation to give total hours by week. I built one for a company I worked for a few year's back, but I don;t have that file anymore. ETA: Here are templates from the Microsoft site, they may help you out ETA Part 2: If you need to have the shift start and end time display in one cell for some reason, you can achieve that by entering the times in separate cells (then simply hide them) so that the calculations work, but then add a concatenate formula in another cell for display purposes. Edited January 5, 2009 by Big Country Quote Link to comment Share on other sites More sharing options...
geeteebee Posted January 5, 2009 Share Posted January 5, 2009 I think the easiest way would be for you to break the shift cells into 2. 1 for start time and 1 for end time and then subtract one from the other. You can use the time number format to get the time to look how you want it. Quote Link to comment Share on other sites More sharing options...
gbpfan1231 Posted January 6, 2009 Share Posted January 6, 2009 Ok so I am making a schedule in Excel and would like to be able to use =Sum function but I can't (or dont know how) because of how the schedule has to be made: Here is what I mean...in one cell I have a shift entered like this: 6:45a-3:45p in another 6:45a-11:30p is there a way that I can get excel to calculate that the 6:45a-3:45p is an 8 hour shift(yeah I need to take out the hour lunch) and to also get it to calculate the 6:45a-11:30p as a 4.75 hour shift? If that can't be done is there a way for me to assign 8 hours to the 6:45a-3:45p cells and so on so that it can calculate the weekly totals for each person as well as the daily totals? I am guessing that you don't have a lot of different shifts so in other words there are not unique items in your shifts column. If so, you could use a VLOOKUP formula. You would need to get all of the unique shifts inot one column (there is a function to do this for you. Then manually put in the number of hours for those specific shifts and use that "Table" to bring in the hours into your original table. For example Column A has the unique shifts column B has the hours for that shift and column C has your original data. A Quote Link to comment Share on other sites More sharing options...
gbpfan1231 Posted January 6, 2009 Share Posted January 6, 2009 (edited) Ok so I am making a schedule in Excel and would like to be able to use =Sum function but I can't (or dont know how) because of how the schedule has to be made: Here is what I mean...in one cell I have a shift entered like this: 6:45a-3:45p in another 6:45a-11:30p is there a way that I can get excel to calculate that the 6:45a-3:45p is an 8 hour shift(yeah I need to take out the hour lunch) and to also get it to calculate the 6:45a-11:30p as a 4.75 hour shift? If that can't be done is there a way for me to assign 8 hours to the 6:45a-3:45p cells and so on so that it can calculate the weekly totals for each person as well as the daily totals? I am guessing that you don't have a lot of different shifts so in other words there are not a lot of unique items in your shifts column. If so, you could use a VLOOKUP formula. You would need to get all of the unique shifts inot one column (there is a function to do this for you. Then manually put in the number of hours for those specific shifts and use that "Table" to bring in the hours into your original table. For example Column A has the unique shifts column B has the hours for that shift and column C has your original data. A B C D 6:45a-3:45p 8 6:45a-3:45p formula = VLOOKUP(C1,$A$:$B$,2,FALSE) result = 8 6:45a-11:30p 4.75 6:45a-11:30p formula = VLOOKUP(C2,$A$:$B$,2,FALSE) result = 4.75 Probably confusing but I would be glad to help over the phone or could even do the formulas for you if you wanted to send an example. Edited January 6, 2009 by gbpfan1231 Quote Link to comment Share on other sites More sharing options...
keggerz Posted January 6, 2009 Author Share Posted January 6, 2009 I am guessing that you don't have a lot of different shifts so in other words there are not a lot of unique items in your shifts column. If so, you could use a VLOOKUP formula. You would need to get all of the unique shifts inot one column (there is a function to do this for you. Then manually put in the number of hours for those specific shifts and use that "Table" to bring in the hours into your original table. For example Column A has the unique shifts column B has the hours for that shift and column C has your original data. A B C D 6:45a-3:45p 8 6:45a-3:45p formula = VLOOKUP(C1,$A$:$B$,2,FALSE) result = 8 6:45a-11:30p 4.75 6:45a-11:30p formula = VLOOKUP(C2,$A$:$B$,2,FALSE) result = 4.75 Probably confusing but I would be glad to help over the phone or could even do the formulas for you if you wanted to send an example. i think this is what I was looking for...I will try it out and if I have any problems I will PM you. THANKS Quote Link to comment Share on other sites More sharing options...
gbpfan1231 Posted January 6, 2009 Share Posted January 6, 2009 i think this is what I was looking for...I will try it out and if I have any problems I will PM you. THANKS Just let me know. Getting the unique shifts use Data =>Filter => Advanced Filter (use copy to another location, select your List Range and then select Unique Records Only) Then just do a VLOOKUP - probably the best formula in Excel. Let me know if you have issues. Quote Link to comment Share on other sites More sharing options...
Brentastic Posted January 8, 2009 Share Posted January 8, 2009 Just let me know. Getting the unique shifts use Data =>Filter => Advanced Filter (use copy to another location, select your List Range and then select Unique Records Only) Then just do a VLOOKUP - probably the best formula in Excel. Let me know if you have issues. I personally like the triple inverse concatenation formula mutiplied by the sum of ISNA divided by VLOOKUP - but that's just me Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.