Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help: Trying to get hours for 11pm-7am shift
I have a third shift and using excel to calculate time 10:30 to 7am all the
other shifts calculate fine useing =sum((A6-A5)*24(A4-A3)*24), But useing this formula the third shift comes out in negative numbers any suggestions. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to get hours for 11pm-7am shift
Your formula isn't valid syntax. If you've got a formula in your
spreadsheet it's safest to copy it from the formula bar to the newsgroup, rather than retyping it with errors. Did you mean =SUM((A6-A5)*24,(A4-A3)*24) ? If your problem is with going beyond midnight, it's worth using the MOD(...,1) construct. Try =24*(MOD(A6-A5,1)+MOD(A4-A3,1)) -- David Biddulph "Caramon6561" wrote in message ... I have a third shift and using excel to calculate time 10:30 to 7am all the other shifts calculate fine useing =sum((A6-A5)*24(A4-A3)*24), But useing this formula the third shift comes out in negative numbers any suggestions. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to get hours for 11pm-7am shift
Yes thats what i meant sorry. what i'm trying to do is basic i guess IN
punch, OUT punch for lunch back IN punch from luck and OUT punch at the end of the day with the total hours for that day and a total for the week at the bottom. "David Biddulph" wrote: Your formula isn't valid syntax. If you've got a formula in your spreadsheet it's safest to copy it from the formula bar to the newsgroup, rather than retyping it with errors. Did you mean =SUM((A6-A5)*24,(A4-A3)*24) ? If your problem is with going beyond midnight, it's worth using the MOD(...,1) construct. Try =24*(MOD(A6-A5,1)+MOD(A4-A3,1)) -- David Biddulph "Caramon6561" wrote in message ... I have a third shift and using excel to calculate time 10:30 to 7am all the other shifts calculate fine useing =sum((A6-A5)*24(A4-A3)*24), But useing this formula the third shift comes out in negative numbers any suggestions. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to get hours for 11pm-7am shift
IT may also help I'm useing Office XP the MOD formula give me times at the end
"David Biddulph" wrote: Your formula isn't valid syntax. If you've got a formula in your spreadsheet it's safest to copy it from the formula bar to the newsgroup, rather than retyping it with errors. Did you mean =SUM((A6-A5)*24,(A4-A3)*24) ? If your problem is with going beyond midnight, it's worth using the MOD(...,1) construct. Try =24*(MOD(A6-A5,1)+MOD(A4-A3,1)) -- David Biddulph "Caramon6561" wrote in message ... I have a third shift and using excel to calculate time 10:30 to 7am all the other shifts calculate fine useing =sum((A6-A5)*24(A4-A3)*24), But useing this formula the third shift comes out in negative numbers any suggestions. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to get hours for 11pm-7am shift
You need to format the cell to be in number format, not in a date format. It
is probably giving you a year in 1900. Also your answer will be in days. To convert it to hours multiply by 24. Minutes multiple by 24 * 60 = 1440. "Caramon6561" wrote: IT may also help I'm useing Office XP the MOD formula give me times at the end "David Biddulph" wrote: Your formula isn't valid syntax. If you've got a formula in your spreadsheet it's safest to copy it from the formula bar to the newsgroup, rather than retyping it with errors. Did you mean =SUM((A6-A5)*24,(A4-A3)*24) ? If your problem is with going beyond midnight, it's worth using the MOD(...,1) construct. Try =24*(MOD(A6-A5,1)+MOD(A4-A3,1)) -- David Biddulph "Caramon6561" wrote in message ... I have a third shift and using excel to calculate time 10:30 to 7am all the other shifts calculate fine useing =sum((A6-A5)*24(A4-A3)*24), But useing this formula the third shift comes out in negative numbers any suggestions. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to get hours for 11pm-7am shift
The cells are in time format useing 24 time.
"Joel" wrote: You need to format the cell to be in number format, not in a date format. It is probably giving you a year in 1900. Also your answer will be in days. To convert it to hours multiply by 24. Minutes multiple by 24 * 60 = 1440. "Caramon6561" wrote: IT may also help I'm useing Office XP the MOD formula give me times at the end "David Biddulph" wrote: Your formula isn't valid syntax. If you've got a formula in your spreadsheet it's safest to copy it from the formula bar to the newsgroup, rather than retyping it with errors. Did you mean =SUM((A6-A5)*24,(A4-A3)*24) ? If your problem is with going beyond midnight, it's worth using the MOD(...,1) construct. Try =24*(MOD(A6-A5,1)+MOD(A4-A3,1)) -- David Biddulph "Caramon6561" wrote in message ... I have a third shift and using excel to calculate time 10:30 to 7am all the other shifts calculate fine useing =sum((A6-A5)*24(A4-A3)*24), But useing this formula the third shift comes out in negative numbers any suggestions. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to get hours for 11pm-7am shift
I think you need to change the results to a number format. Time starts Jan
1, 1900. If you are trying to get the difference between two dates 4/15/07 - 4/14/07 this gives you 1 which in time format is Jan 1, 1900. In number format it gives you a 1. "Caramon6561" wrote: The cells are in time format useing 24 time. "Joel" wrote: You need to format the cell to be in number format, not in a date format. It is probably giving you a year in 1900. Also your answer will be in days. To convert it to hours multiply by 24. Minutes multiple by 24 * 60 = 1440. "Caramon6561" wrote: IT may also help I'm useing Office XP the MOD formula give me times at the end "David Biddulph" wrote: Your formula isn't valid syntax. If you've got a formula in your spreadsheet it's safest to copy it from the formula bar to the newsgroup, rather than retyping it with errors. Did you mean =SUM((A6-A5)*24,(A4-A3)*24) ? If your problem is with going beyond midnight, it's worth using the MOD(...,1) construct. Try =24*(MOD(A6-A5,1)+MOD(A4-A3,1)) -- David Biddulph "Caramon6561" wrote in message ... I have a third shift and using excel to calculate time 10:30 to 7am all the other shifts calculate fine useing =sum((A6-A5)*24(A4-A3)*24), But useing this formula the third shift comes out in negative numbers any suggestions. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Trying to get hours for 11pm-7am shift
Thanks Joel I feel so sheepish... DAH...
"Joel" wrote: I think you need to change the results to a number format. Time starts Jan 1, 1900. If you are trying to get the difference between two dates 4/15/07 - 4/14/07 this gives you 1 which in time format is Jan 1, 1900. In number format it gives you a 1. "Caramon6561" wrote: The cells are in time format useing 24 time. "Joel" wrote: You need to format the cell to be in number format, not in a date format. It is probably giving you a year in 1900. Also your answer will be in days. To convert it to hours multiply by 24. Minutes multiple by 24 * 60 = 1440. "Caramon6561" wrote: IT may also help I'm useing Office XP the MOD formula give me times at the end "David Biddulph" wrote: Your formula isn't valid syntax. If you've got a formula in your spreadsheet it's safest to copy it from the formula bar to the newsgroup, rather than retyping it with errors. Did you mean =SUM((A6-A5)*24,(A4-A3)*24) ? If your problem is with going beyond midnight, it's worth using the MOD(...,1) construct. Try =24*(MOD(A6-A5,1)+MOD(A4-A3,1)) -- David Biddulph "Caramon6561" wrote in message ... I have a third shift and using excel to calculate time 10:30 to 7am all the other shifts calculate fine useing =sum((A6-A5)*24(A4-A3)*24), But useing this formula the third shift comes out in negative numbers any suggestions. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Total hours worked during workdays within given shift time. | Excel Discussion (Misc queries) | |||
Program for assigning work shift hours | Excel Discussion (Misc queries) | |||
IS THERE A FORMULA TO MAKE E=6:00PM ETC SHIFT TIMES AND HOURS | Excel Worksheet Functions | |||
How do I show schedule 11pm to 8 am in Excel? | Excel Discussion (Misc queries) | |||
Need a function that separates over-lapping work shift hours. | Excel Worksheet Functions |