| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula is driving me crazy?????
I have emailed Christ directly for his workbook. If I can help, I'll provide
the solution here. If I can't, I'll upload it and provide a link... :) ************ Hope it helps! Anne Troy www.OfficeArticles.com Check out the NEWsgroup stats! Check out: www.ExcelUserConference.com "Chris Watson" wrote in message news:Chris.Watson.232v3y_1139682602.5497@excelforu m-nospam.com... Here is my workbook, Orange cells mark the -- Chris Watson ------------------------------------------------------------------------ Chris Watson's Profile: http://www.excelforum.com/member.php...fo&userid=4700 View this thread: http://www.excelforum.com/showthread...hreadid=511313 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula is driving me crazy?????
After tweaking, I've uploaded it he
http://www.officearticles.com/temp/chris.zip Your first problem is that the cells in which you enter start and end time have no dates. Since you're obviously spanning over midnight, you must tell Excel the date as well or it believes the day is the same day. Second problem is that all cells EXCEPT the time entry cells must be formatted as [h]:mm because you want them to be ADDING hours, and not calculating what the time would be after those hours are added. :) ************ Hope it helps! Anne Troy www.OfficeArticles.com Check out the NEWsgroup stats! Check out: www.ExcelUserConference.com "Anne Troy" wrote in message ... I have emailed Christ directly for his workbook. If I can help, I'll provide the solution here. If I can't, I'll upload it and provide a link... :) ************ Hope it helps! Anne Troy www.OfficeArticles.com Check out the NEWsgroup stats! Check out: www.ExcelUserConference.com "Chris Watson" wrote in message news:Chris.Watson.232v3y_1139682602.5497@excelforu m-nospam.com... Here is my workbook, Orange cells mark the -- Chris Watson ------------------------------------------------------------------------ Chris Watson's Profile: http://www.excelforum.com/member.php...fo&userid=4700 View this thread: http://www.excelforum.com/showthread...hreadid=511313 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula is driving me crazy?????
Anne this is brilliant, your the headache tablet ive been looking for, but I have a question ? is it possible to just type in the times in Start and Finish without putting the date in, This timesheet is designed for night shift work only so I will always start one day and finish the next. Also there is one more problem thats bugging me and that is if you look at cell U28, this is whats left of 48 hours POA in a week, the law says that a driver of a HGV cannot exceed 48 hours POA in a week and needs to know whats left. Cell L23 is the total POA of the week so I need to take this figure away from 48:00, all I get is #######. -- Chris Watson ------------------------------------------------------------------------ Chris Watson's Profile: http://www.excelforum.com/member.php...fo&userid=4700 View this thread: http://www.excelforum.com/showthread...hreadid=511313 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula is driving me crazy?????
You're not really going to get away without putting in the date, and working
around that (having the date and time in 2 separate cells) will take someone better than me. :) It looks like POA will also work out if you format the cells as [h]:mm. And you cannot have a "negative" time, really, so you need to handle that as well. ************ Hope it helps! Anne Troy www.OfficeArticles.com Check out the NEWsgroup stats! Check out: www.ExcelUserConference.com "Chris Watson" wrote in message news:Chris.Watson.2343jz_1139740202.9775@excelforu m-nospam.com... Anne this is brilliant, your the headache tablet ive been looking for, but I have a question ? is it possible to just type in the times in Start and Finish without putting the date in, This timesheet is designed for night shift work only so I will always start one day and finish the next. Also there is one more problem thats bugging me and that is if you look at cell U28, this is whats left of 48 hours POA in a week, the law says that a driver of a HGV cannot exceed 48 hours POA in a week and needs to know whats left. Cell L23 is the total POA of the week so I need to take this figure away from 48:00, all I get is #######. -- Chris Watson ------------------------------------------------------------------------ Chris Watson's Profile: http://www.excelforum.com/member.php...fo&userid=4700 View this thread: http://www.excelforum.com/showthread...hreadid=511313 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula is driving me crazy?????
I didn't say what I really meant:
If as Chris says the time are ALWAYS overnight then: If the times are ALWAYS the same day or overnght, (ie never more than 24 hours between the Start/End times), then the formulas will return the elapsed time without adding dates. -- HTH Sandy with @tiscali.co.uk "Sandy Mann" wrote in message ... You're not really going to get away without putting in the date, If as Chris says the time are ALWAYS overnight then: =MOD(Start Time - End time),1) or =Start Time - End time + (End Time < Start Time) -- HTH Sandy with @tiscali.co.uk "Anne Troy" wrote in message ... You're not really going to get away without putting in the date, and working around that (having the date and time in 2 separate cells) will take someone better than me. :) It looks like POA will also work out if you format the cells as [h]:mm. And you cannot have a "negative" time, really, so you need to handle that as well. ************ Hope it helps! Anne Troy www.OfficeArticles.com Check out the NEWsgroup stats! Check out: www.ExcelUserConference.com "Chris Watson" wrote in message news:Chris.Watson.2343jz_1139740202.9775@excelforu m-nospam.com... Anne this is brilliant, your the headache tablet ive been looking for, but I have a question ? is it possible to just type in the times in Start and Finish without putting the date in, This timesheet is designed for night shift work only so I will always start one day and finish the next. Also there is one more problem thats bugging me and that is if you look at cell U28, this is whats left of 48 hours POA in a week, the law says that a driver of a HGV cannot exceed 48 hours POA in a week and needs to know whats left. Cell L23 is the total POA of the week so I need to take this figure away from 48:00, all I get is #######. -- Chris Watson ------------------------------------------------------------------------ Chris Watson's Profile: http://www.excelforum.com/member.php...fo&userid=4700 View this thread: http://www.excelforum.com/showthread...hreadid=511313 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula is driving me crazy?????
Would it be possible to use AM & PM when entering times????? -- Chris Watson ------------------------------------------------------------------------ Chris Watson's Profile: http://www.excelforum.com/member.php...fo&userid=4700 View this thread: http://www.excelforum.com/showthread...hreadid=511313 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sorry for reposting - but this is driving me crazy! | Excel Discussion (Misc queries) | |||
| HEEEEEEEEEELP!! This thing is driving me crazy. | Excel Discussion (Misc queries) | |||
| My Shared workbook is driving me crazy!!! | Excel Discussion (Misc queries) | |||
| Help: Scroll wheel toggles between up and down, and left and right - is driving me crazy | Excel Discussion (Misc queries) | |||
| Date Comparison Driving Me Crazy | Excel Discussion (Misc queries) | |||
Hybrid Mode
