| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Subtracting 24hr clocks?
HI, HELP!
I'm trying to generate the formula to subtract 24hr time clocks. If I want to work out the hours between 0500 and 1600 then it's a simple case of subtraction - however, the problem is when I try and reverse this. I'm trying to calculate a formula to work out the hours between, for example, 1600 and 0630 - the formula I've tried using doesn't work so is obviously wrong. Please help! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting 24hr clocks?
start time:A9
end time:B9 =B9+IF(A9B9,1,0)-A9 Regards, Stefi €˛devoted_darling€¯ ezt Ć*rta: HI, HELP! I'm trying to generate the formula to subtract 24hr time clocks. If I want to work out the hours between 0500 and 1600 then it's a simple case of subtraction - however, the problem is when I try and reverse this. I'm trying to calculate a formula to work out the hours between, for example, 1600 and 0630 - the formula I've tried using doesn't work so is obviously wrong. Please help! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting 24hr clocks?
=MOD(B1-A1,1)
-- David Biddulph "devoted_darling" wrote in message ... HI, HELP! I'm trying to generate the formula to subtract 24hr time clocks. If I want to work out the hours between 0500 and 1600 then it's a simple case of subtraction - however, the problem is when I try and reverse this. I'm trying to calculate a formula to work out the hours between, for example, 1600 and 0630 - the formula I've tried using doesn't work so is obviously wrong. Please help! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting 24hr clocks?
Hi Stefi,
This doesn't seem to work?! I'm trying to work out the hours between 1600 (start) and 0800 (end) but it's coming out with a minus figure. Is there any way I can do this? Thanks Clare "Stefi" wrote: start time:A9 end time:B9 =B9+IF(A9B9,1,0)-A9 Regards, Stefi €˛devoted_darling€¯ ezt Ć*rta: HI, HELP! I'm trying to generate the formula to subtract 24hr time clocks. If I want to work out the hours between 0500 and 1600 then it's a simple case of subtraction - however, the problem is when I try and reverse this. I'm trying to calculate a formula to work out the hours between, for example, 1600 and 0630 - the formula I've tried using doesn't work so is obviously wrong. Please help! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting 24hr clocks?
Hi David,
Thankyou for your reply, however the formula brings back 0 when it whould bring back a number as it's calculating the hours worked between the 2 hours posted..... Any ideas? Clare "David Biddulph" wrote: =MOD(B1-A1,1) -- David Biddulph "devoted_darling" wrote in message ... HI, HELP! I'm trying to generate the formula to subtract 24hr time clocks. If I want to work out the hours between 0500 and 1600 then it's a simple case of subtraction - however, the problem is when I try and reverse this. I'm trying to calculate a formula to work out the hours between, for example, 1600 and 0630 - the formula I've tried using doesn't work so is obviously wrong. Please help! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting 24hr clocks?
I have a guess: 1600 (start) and 0800 (end) suggests that your data are not
of real Excel time format but text strings, otherwise they would look like 16:00 (start) and 08:00 (end). Am I right? Stefi €˛devoted_darling€¯ ezt Ć*rta: Hi Stefi, This doesn't seem to work?! I'm trying to work out the hours between 1600 (start) and 0800 (end) but it's coming out with a minus figure. Is there any way I can do this? Thanks Clare "Stefi" wrote: start time:A9 end time:B9 =B9+IF(A9B9,1,0)-A9 Regards, Stefi €˛devoted_darling€¯ ezt Ć*rta: HI, HELP! I'm trying to generate the formula to subtract 24hr time clocks. If I want to work out the hours between 0500 and 1600 then it's a simple case of subtraction - however, the problem is when I try and reverse this. I'm trying to calculate a formula to work out the hours between, for example, 1600 and 0630 - the formula I've tried using doesn't work so is obviously wrong. Please help! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting 24hr clocks?
I wonder whether you entered your times correctly? Remember that Excel
times are entered as 16:00 and 06:30, not 1600 and 0630. -- David Biddulph "devoted_darling" wrote in message ... Hi David, Thankyou for your reply, however the formula brings back 0 when it whould bring back a number as it's calculating the hours worked between the 2 hours posted..... Any ideas? Clare "David Biddulph" wrote: =MOD(B1-A1,1) -- David Biddulph "devoted_darling" wrote in message ... HI, HELP! I'm trying to generate the formula to subtract 24hr time clocks. If I want to work out the hours between 0500 and 1600 then it's a simple case of subtraction - however, the problem is when I try and reverse this. I'm trying to calculate a formula to work out the hours between, for example, 1600 and 0630 - the formula I've tried using doesn't work so is obviously wrong. Please help! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting 24hr clocks?
Hi Stefi, yes that's correct. They are currently in text format - do they
need to be changed to time format for this to work? Clare "Stefi" wrote: I have a guess: 1600 (start) and 0800 (end) suggests that your data are not of real Excel time format but text strings, otherwise they would look like 16:00 (start) and 08:00 (end). Am I right? Stefi €˛devoted_darling€¯ ezt Ć*rta: Hi Stefi, This doesn't seem to work?! I'm trying to work out the hours between 1600 (start) and 0800 (end) but it's coming out with a minus figure. Is there any way I can do this? Thanks Clare "Stefi" wrote: start time:A9 end time:B9 =B9+IF(A9B9,1,0)-A9 Regards, Stefi €˛devoted_darling€¯ ezt Ć*rta: HI, HELP! I'm trying to generate the formula to subtract 24hr time clocks. If I want to work out the hours between 0500 and 1600 then it's a simple case of subtraction - however, the problem is when I try and reverse this. I'm trying to calculate a formula to work out the hours between, for example, 1600 and 0630 - the formula I've tried using doesn't work so is obviously wrong. Please help! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting 24hr clocks?
Hi David,
yes the times have been entered incorrectly - unfortunately I'm working from someone elses data so they have been entered as 1600 instead of 16:00. I'll change them and try again! Thanks Clare "David Biddulph" wrote: I wonder whether you entered your times correctly? Remember that Excel times are entered as 16:00 and 06:30, not 1600 and 0630. -- David Biddulph "devoted_darling" wrote in message ... Hi David, Thankyou for your reply, however the formula brings back 0 when it whould bring back a number as it's calculating the hours worked between the 2 hours posted..... Any ideas? Clare "David Biddulph" wrote: =MOD(B1-A1,1) -- David Biddulph "devoted_darling" wrote in message ... HI, HELP! I'm trying to generate the formula to subtract 24hr time clocks. If I want to work out the hours between 0500 and 1600 then it's a simple case of subtraction - however, the problem is when I try and reverse this. I'm trying to calculate a formula to work out the hours between, for example, 1600 and 0630 - the formula I've tried using doesn't work so is obviously wrong. Please help! |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting 24hr clocks?
Yes, convert them with this function:
=TIMEVALUE(LEFT(A1,2)&":"&RIGHT(A1,2)) and format the result cells like "hh:mm"! Use David's formula because it's nicer! Regards, Stefi €˛devoted_darling€¯ ezt Ć*rta: Hi Stefi, yes that's correct. They are currently in text format - do they need to be changed to time format for this to work? Clare "Stefi" wrote: I have a guess: 1600 (start) and 0800 (end) suggests that your data are not of real Excel time format but text strings, otherwise they would look like 16:00 (start) and 08:00 (end). Am I right? Stefi €˛devoted_darling€¯ ezt Ć*rta: Hi Stefi, This doesn't seem to work?! I'm trying to work out the hours between 1600 (start) and 0800 (end) but it's coming out with a minus figure. Is there any way I can do this? Thanks Clare "Stefi" wrote: start time:A9 end time:B9 =B9+IF(A9B9,1,0)-A9 Regards, Stefi €˛devoted_darling€¯ ezt Ć*rta: HI, HELP! I'm trying to generate the formula to subtract 24hr time clocks. If I want to work out the hours between 0500 and 1600 then it's a simple case of subtraction - however, the problem is when I try and reverse this. I'm trying to calculate a formula to work out the hours between, for example, 1600 and 0630 - the formula I've tried using doesn't work so is obviously wrong. Please help! |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting 24hr clocks?
To convert your incorrectly entered times, use =--TEXT(A1,"00\:00") and
format as time appropriately. -- David Biddulph "devoted_darling" wrote in message ... Hi David, yes the times have been entered incorrectly - unfortunately I'm working from someone elses data so they have been entered as 1600 instead of 16:00. I'll change them and try again! Thanks Clare "David Biddulph" wrote: I wonder whether you entered your times correctly? Remember that Excel times are entered as 16:00 and 06:30, not 1600 and 0630. -- David Biddulph "devoted_darling" wrote in message ... Hi David, Thankyou for your reply, however the formula brings back 0 when it whould bring back a number as it's calculating the hours worked between the 2 hours posted..... Any ideas? Clare "David Biddulph" wrote: =MOD(B1-A1,1) -- David Biddulph "devoted_darling" wrote in message ... HI, HELP! I'm trying to generate the formula to subtract 24hr time clocks. If I want to work out the hours between 0500 and 1600 then it's a simple case of subtraction - however, the problem is when I try and reverse this. I'm trying to calculate a formula to work out the hours between, for example, 1600 and 0630 - the formula I've tried using doesn't work so is obviously wrong. Please help! |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Subtracting 24hr clocks?
David,
I've changed the format to 16:00 etc and it's working brilliantly now! Thanku Clare :) "devoted_darling" wrote: Hi David, yes the times have been entered incorrectly - unfortunately I'm working from someone elses data so they have been entered as 1600 instead of 16:00. I'll change them and try again! Thanks Clare "David Biddulph" wrote: I wonder whether you entered your times correctly? Remember that Excel times are entered as 16:00 and 06:30, not 1600 and 0630. -- David Biddulph "devoted_darling" wrote in message ... Hi David, Thankyou for your reply, however the formula brings back 0 when it whould bring back a number as it's calculating the hours worked between the 2 hours posted..... Any ideas? Clare "David Biddulph" wrote: =MOD(B1-A1,1) -- David Biddulph "devoted_darling" wrote in message ... HI, HELP! I'm trying to generate the formula to subtract 24hr time clocks. If I want to work out the hours between 0500 and 1600 then it's a simple case of subtraction - however, the problem is when I try and reverse this. I'm trying to calculate a formula to work out the hours between, for example, 1600 and 0630 - the formula I've tried using doesn't work so is obviously wrong. Please help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formating 24hr Clock, but not as time!! | Excel Discussion (Misc queries) | |||
| Creating running clocks in multiple cells. | Excel Worksheet Functions | |||
| Converting format of hh:mm AM/PM to only hh:mm non 24hr in same co | Excel Discussion (Misc queries) | |||
| NEED A FORMULA TO CALCULATE DIFFERENCE BETWEEN TWO TIMES IN 24HR . | New Users to Excel | |||
| 24hr moving average | Excel Discussion (Misc queries) | |||
Linear Mode
