Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying time - Error in formula (I think?)
I have a spread sheet for tracking employees time and for some days the
fields are displayed correct but in others (using the same format) the hours are out of whack! For example: Cell d7 = 19:30 e7 = 05:30 (should be 10 hours) Cell d6 = 10:00 e6 = 20:00 (should be 10 hours) Cell d5 = 05:30 d5 = 15:30 (should be 10 hours) When I add the totals together it shows 6:30 hours, I've tried several different formulas from this website and none of them work on this particular problem. My spreadsheet uses 2 colums per employee per day (start & end) times. It starts in column B and ends in column O (7 days for the week). It seems that if the total number of hours for the above 3 employees exceeds 24 hours then the time gets messed up. Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying time - Error in formula (I think?)
Format the result cell as [h]:mm
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "El Bee" wrote in message ... I have a spread sheet for tracking employees time and for some days the fields are displayed correct but in others (using the same format) the hours are out of whack! For example: Cell d7 = 19:30 e7 = 05:30 (should be 10 hours) Cell d6 = 10:00 e6 = 20:00 (should be 10 hours) Cell d5 = 05:30 d5 = 15:30 (should be 10 hours) When I add the totals together it shows 6:30 hours, I've tried several different formulas from this website and none of them work on this particular problem. My spreadsheet uses 2 colums per employee per day (start & end) times. It starts in column B and ends in column O (7 days for the week). It seems that if the total number of hours for the above 3 employees exceeds 24 hours then the time gets messed up. Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying time - Error in formula (I think?)
Hi
With start time p.e. in D5 and end time in E5 =E5-D5+(E5<D5) NB! Both time columns and the column with formula must be formatted in a valid time format. When you want to sum formula results, format this cell as custom "[h]:mm" or "[h]:mm:ss" Arvi Laanemets "El Bee" wrote in message ... I have a spread sheet for tracking employees time and for some days the fields are displayed correct but in others (using the same format) the hours are out of whack! For example: Cell d7 = 19:30 e7 = 05:30 (should be 10 hours) Cell d6 = 10:00 e6 = 20:00 (should be 10 hours) Cell d5 = 05:30 d5 = 15:30 (should be 10 hours) When I add the totals together it shows 6:30 hours, I've tried several different formulas from this website and none of them work on this particular problem. My spreadsheet uses 2 colums per employee per day (start & end) times. It starts in column B and ends in column O (7 days for the week). It seems that if the total number of hours for the above 3 employees exceeds 24 hours then the time gets messed up. Any ideas? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying time - Error in formula (I think?)
Here's what I have in the two columns with the total at the bottom.
col D col E ------ ------ Start End 5:30 15:30 10:00 20:00 19:00 5:30 TOTAL 6:30 Here's what I get when I change the format to [h]:mm Start End 5:30 15:30 10:00 20:00 19:00 5:30 TOTAL 606:30 Here's the formula =MOD(E5-D5,1)+ MOD(E6-D6,1) + MOD(E7-D7,1)+ MOD(E8-D8,1)+ MOD(E9-D9,1)+ MOD(E10-D10,1)+ MOD(E11-D11,1) "Arvi Laanemets" wrote: Hi With start time p.e. in D5 and end time in E5 =E5-D5+(E5<D5) NB! Both time columns and the column with formula must be formatted in a valid time format. When you want to sum formula results, format this cell as custom "[h]:mm" or "[h]:mm:ss" Arvi Laanemets "El Bee" wrote in message ... I have a spread sheet for tracking employees time and for some days the fields are displayed correct but in others (using the same format) the hours are out of whack! For example: Cell d7 = 19:30 e7 = 05:30 (should be 10 hours) Cell d6 = 10:00 e6 = 20:00 (should be 10 hours) Cell d5 = 05:30 d5 = 15:30 (should be 10 hours) When I add the totals together it shows 6:30 hours, I've tried several different formulas from this website and none of them work on this particular problem. My spreadsheet uses 2 colums per employee per day (start & end) times. It starts in column B and ends in column O (7 days for the week). It seems that if the total number of hours for the above 3 employees exceeds 24 hours then the time gets messed up. Any ideas? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Displaying time - Error in formula (I think?)
Arvi,
Disregard the previous email. I found the problem. I had a different format in one of the cells and the formula had an error; as well. Thanks for pointing me in the right direction. "Arvi Laanemets" wrote: Hi With start time p.e. in D5 and end time in E5 =E5-D5+(E5<D5) NB! Both time columns and the column with formula must be formatted in a valid time format. When you want to sum formula results, format this cell as custom "[h]:mm" or "[h]:mm:ss" Arvi Laanemets "El Bee" wrote in message ... I have a spread sheet for tracking employees time and for some days the fields are displayed correct but in others (using the same format) the hours are out of whack! For example: Cell d7 = 19:30 e7 = 05:30 (should be 10 hours) Cell d6 = 10:00 e6 = 20:00 (should be 10 hours) Cell d5 = 05:30 d5 = 15:30 (should be 10 hours) When I add the totals together it shows 6:30 hours, I've tried several different formulas from this website and none of them work on this particular problem. My spreadsheet uses 2 colums per employee per day (start & end) times. It starts in column B and ends in column O (7 days for the week). It seems that if the total number of hours for the above 3 employees exceeds 24 hours then the time gets messed up. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
$ vs time formula | Excel Worksheet Functions | |||
Help with time formula so the time will not change. | Excel Discussion (Misc queries) | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula is entering a default time when it comes across an empty cell.. | Excel Worksheet Functions | |||
Formula to deduct unpaid breaks in time sheet | Excel Discussion (Misc queries) |