help creating a time function
in [Functions]
|
Prev: Is there a function in excel where you can the beginning of the mo
Next: How do I change date that is in All Caps to proper format?
From: Per Jessen on 21 Jan 2010 22:18 Thanks for your reply. Sure, =SUM(A2:D2) work on time, but if the result exceeds 24 hours, you have to format the resulting cell as Custom: [h]:mm to show the result as hours. (Goto Format > Cells > Number > Custom > Type: [h]:mm) Regards, Per On 22 Jan., 03:48, Josh <J...(a)discussions.microsoft.com> wrote: > Per, > > That worked PERFECTLY!!! Thank you!!! > > Now one more question. I am also trying to add up the time but the simple > =sum (a2:d2) doesnt work. Is there a special one for time? > > > > "Per Jessen" wrote: > > Hi > > > =A1-TIMEVALUE("8:00") > > > Format the resulting cell as time. > > > Regards, > > Per > > > On 22 Jan., 02:29, Josh <J...(a)discussions.microsoft.com> wrote: > > > Hello, > > > I am trying to create a time function that will look at a typical day and > > > add the hours. Then show how many hours after 8 is overtime. I have the > > > first part created, but how can I write a formula that will look at say 12:45 > > > hours and tell me 4:45 of it is overtime? Please help!! > > > > Thanks!!! > > > .- Skjul tekst i anførselstegn - > > - Vis tekst i anførselstegn -
From: Josh on 4 Feb 2010 22:58 Hello Per, I have tried this method and it still comes up as 0:00. I even made the custom [h]:mm and used the h:mm format. Here is what the cell format is currently =SUM(J2:J34) It is formatted as [h]:mm "Per Jessen" wrote: > Thanks for your reply. > > Sure, =SUM(A2:D2) work on time, but if the result exceeds 24 hours, > you have to format the resulting cell as Custom: [h]:mm to show the > result as hours. > (Goto Format > Cells > Number > Custom > Type: [h]:mm) > > > Regards, > Per > > On 22 Jan., 03:48, Josh <J...(a)discussions.microsoft.com> wrote: > > Per, > > > > That worked PERFECTLY!!! Thank you!!! > > > > Now one more question. I am also trying to add up the time but the simple > > =sum (a2:d2) doesnt work. Is there a special one for time? > > > > > > > > "Per Jessen" wrote: > > > Hi > > > > > =A1-TIMEVALUE("8:00") > > > > > Format the resulting cell as time. > > > > > Regards, > > > Per > > > > > On 22 Jan., 02:29, Josh <J...(a)discussions.microsoft.com> wrote: > > > > Hello, > > > > I am trying to create a time function that will look at a typical day and > > > > add the hours. Then show how many hours after 8 is overtime. I have the > > > > first part created, but how can I write a formula that will look at say 12:45 > > > > hours and tell me 4:45 of it is overtime? Please help!! > > > > > > Thanks!!! > > > > > .- Skjul tekst i anførselstegn - > > > > - Vis tekst i anførselstegn - > > . >
From: Josh on 4 Feb 2010 23:00 I just noticed that if it is actual time it works. What I am trying to caculate is the extra time with this formula. =TEXT(E30+I30,"h:mm") How can I add that time up? "Per Jessen" wrote: > Thanks for your reply. > > Sure, =SUM(A2:D2) work on time, but if the result exceeds 24 hours, > you have to format the resulting cell as Custom: [h]:mm to show the > result as hours. > (Goto Format > Cells > Number > Custom > Type: [h]:mm) > > > Regards, > Per > > On 22 Jan., 03:48, Josh <J...(a)discussions.microsoft.com> wrote: > > Per, > > > > That worked PERFECTLY!!! Thank you!!! > > > > Now one more question. I am also trying to add up the time but the simple > > =sum (a2:d2) doesnt work. Is there a special one for time? > > > > > > > > "Per Jessen" wrote: > > > Hi > > > > > =A1-TIMEVALUE("8:00") > > > > > Format the resulting cell as time. > > > > > Regards, > > > Per > > > > > On 22 Jan., 02:29, Josh <J...(a)discussions.microsoft.com> wrote: > > > > Hello, > > > > I am trying to create a time function that will look at a typical day and > > > > add the hours. Then show how many hours after 8 is overtime. I have the > > > > first part created, but how can I write a formula that will look at say 12:45 > > > > hours and tell me 4:45 of it is overtime? Please help!! > > > > > > Thanks!!! > > > > > .- Skjul tekst i anførselstegn - > > > > - Vis tekst i anførselstegn - > > . >
From: "David Biddulph" groups [at] on 5 Feb 2010 03:57
If you want to add it up, you need to make it a time, not text, so =E30+I30 (and format as [h]:mm) -- David Biddulph Josh wrote: > I just noticed that if it is actual time it works. What I am trying > to caculate is the extra time with this formula. > =TEXT(E30+I30,"h:mm") How can I add that time up? > > "Per Jessen" wrote: > >> Thanks for your reply. >> >> Sure, =SUM(A2:D2) work on time, but if the result exceeds 24 hours, >> you have to format the resulting cell as Custom: [h]:mm to show the >> result as hours. >> (Goto Format > Cells > Number > Custom > Type: [h]:mm) >> >> >> Regards, >> Per >> >> On 22 Jan., 03:48, Josh <J...(a)discussions.microsoft.com> wrote: >>> Per, >>> >>> That worked PERFECTLY!!! Thank you!!! >>> >>> Now one more question. I am also trying to add up the time but the >>> simple =sum (a2:d2) doesnt work. Is there a special one for time? >>> >>> >>> >>> "Per Jessen" wrote: >>>> Hi >>> >>>> =A1-TIMEVALUE("8:00") >>> >>>> Format the resulting cell as time. >>> >>>> Regards, >>>> Per >>> >>>> On 22 Jan., 02:29, Josh <J...(a)discussions.microsoft.com> wrote: >>>>> Hello, >>>>> I am trying to create a time function that will look at a typical >>>>> day and add the hours. Then show how many hours after 8 is >>>>> overtime. I have the first part created, but how can I write a >>>>> formula that will look at say 12:45 hours and tell me 4:45 of it >>>>> is overtime? Please help!! >>> >>>>> Thanks!!! >>> >>>> .- Skjul tekst i anf�rselstegn - >>> >>> - Vis tekst i anf�rselstegn - >> >> . |