Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Time In Decimals
Can anyone help me do this? D1-B1-C1=8.5 hours for the day. How do I format the cells so that excel knows that 0.5 = 1/2 hour. As I have it right now I get a return of something like this (#######). The example below should make what I'm trying to do clear. Don't mind the dashes, I put them in there to help represent the columns and rows. Any help would be greatly appriciated. -------A------------B--------C--------D--------E------------F 1-----Date--------Time in-- Lunch---Time out---Hours-- Hours to Date 2-----01/17/05----8:00-----0.5------17:00------8.5----8.5 3-----01/18/05----7:30-----0.5------17:00------9------17.5 -- Superslinky |
#2
|
|||
|
|||
Hi
You have to know a little theory on this. 1 is a day in excel. Which makes 0.5 half a day, 12 hours, and 8:30 the same as 1/3. See http://www.cpearson.com/excel/datetime.htm for reading and samples. When you get used to it it's very simple from there. HTH. Best wishes Harald "Superslinky" skrev i melding ... Can anyone help me do this? D1-B1-C1=8.5 hours for the day. How do I format the cells so that excel knows that 0.5 = 1/2 hour. As I have it right now I get a return of something like this (#######). The example below should make what I'm trying to do clear. Don't mind the dashes, I put them in there to help represent the columns and rows. Any help would be greatly appriciated. -------A------------B--------C--------D--------E------------F 1-----Date--------Time in-- Lunch---Time out---Hours-- Hours to Date 2-----01/17/05----8:00-----0.5------17:00------8.5----8.5 3-----01/18/05----7:30-----0.5------17:00------9------17.5 -- Superslinky |
#3
|
|||
|
|||
Sorry, I do not have a solution to your question, but would like to increase
the complexity of it just in case any one answers (as I have a similar question). In addition I would like the following; Start time: 09.15 End time 10.50 Time span: 10.50-09.15=1hr 35min. I need to round up every started minute after each half hour to be counted as another half hr. i.e. 1hr 35min should become 2hrs and so on. Besides the figure 2 hrs in the column should be formatted automatically as a digit/number and not time. I want this number to be multiplied by a sum per half hour to give get an emolument sum. I would be greatly thankful for any tips. "Superslinky" wrote Can anyone help me do this? D1-B1-C1=8.5 hours for the day. How do I format the cells so that excel knows that 0.5 = 1/2 hour. As I have it right now I get a return of something like this (#######). The example below should make what I'm trying to do clear. Don't mind the dashes, I put them in there to help represent the columns and rows. Any help would be greatly appriciated. -------A------------B--------C--------D--------E------------F 1-----Date--------Time in-- Lunch---Time out---Hours-- Hours to Date 2-----01/17/05----8:00-----0.5------17:00------8.5----8.5 3-----01/18/05----7:30-----0.5------17:00------9------17.5 -- Superslinky |
#4
|
|||
|
|||
Hi
=CEILING(A1*24,0.5) where A1 is the time, replace it with your actual calculation. HTH. Best wishes Harald "Ravinder S. Mahoon" skrev i melding ... Sorry, I do not have a solution to your question, but would like to increase the complexity of it just in case any one answers (as I have a similar question). In addition I would like the following; Start time: 09.15 End time 10.50 Time span: 10.50-09.15=1hr 35min. I need to round up every started minute after each half hour to be counted as another half hr. i.e. 1hr 35min should become 2hrs and so on. Besides the figure 2 hrs in the column should be formatted automatically as a digit/number and not time. I want this number to be multiplied by a sum per half hour to give get an emolument sum. I would be greatly thankful for any tips. "Superslinky" wrote Can anyone help me do this? D1-B1-C1=8.5 hours for the day. How do I format the cells so that excel knows that 0.5 = 1/2 hour. As I have it right now I get a return of something like this (#######). The example below should make what I'm trying to do clear. Don't mind the dashes, I put them in there to help represent the columns and rows. Any help would be greatly appriciated. -------A------------B--------C--------D--------E------------F 1-----Date--------Time in-- Lunch---Time out---Hours-- Hours to Date 2-----01/17/05----8:00-----0.5------17:00------8.5----8.5 3-----01/18/05----7:30-----0.5------17:00------9------17.5 -- Superslinky |
#5
|
|||
|
|||
In article ,
"Harald Staff" wrote: You have to know a little theory on this. 1 is a day in excel. Which makes 0.5 half a day, 12 hours, and 8:30 the same as 1/3. Starting the day at 00:30, Harald? <g |
#6
|
|||
|
|||
"JE McGimpsey" skrev i melding
... In article , "Harald Staff" wrote: You have to know a little theory on this. 1 is a day in excel. Which makes 0.5 half a day, 12 hours, and 8:30 the same as 1/3. Starting the day at 00:30, Harald? <g Doh!!! 1/3 = 8:00 Sorry & thanks. Best wishes Harald |
#7
|
|||
|
|||
"Harald Staff" wrote: Hi =CEILING(A1*24,0.5) where A1 is the time, replace it with your actual calculation. HTH. Best wishes Harald "Ravinder S. Mahoon" skrev i melding ... Sorry, I do not have a solution to your question, but would like to increase the complexity of it just in case any one answers (as I have a similar question). In addition I would like the following; Start time: 09.15 End time 10.50 Time span: 10.50-09.15=1hr 35min. I need to round up every started minute after each half hour to be counted as another half hr. i.e. 1hr 35min should become 2hrs and so on. Besides the figure 2 hrs in the column should be formatted automatically as a digit/number and not time. I want this number to be multiplied by a sum per half hour to give get an emolument sum. I would be greatly thankful for any tips. "Superslinky" wrote Can anyone help me do this? D1-B1-C1=8.5 hours for the day. How do I format the cells so that excel knows that 0.5 = 1/2 hour. As I have it right now I get a return of something like this (#######). The example below should make what I'm trying to do clear. Don't mind the dashes, I put them in there to help represent the columns and rows. Any help would be greatly appriciated. -------A------------B--------C--------D--------E------------F 1-----Date--------Time in-- Lunch---Time out---Hours-- Hours to Date 2-----01/17/05----8:00-----0.5------17:00------8.5----8.5 3-----01/18/05----7:30-----0.5------17:00------9------17.5 -- Superslinky I dont have an answer but a Question I am looking for a formula to caculate end value using cell reference the numbers in the cell are 281841.00 and i need the value of 2641287.83 |
#8
|
|||
|
|||
Thanks Herald for an excellent link and the solution. The thing is there are
people who learn patiently and methodically and there are others like me who start fumbling when the need arizes. I am trying to get better. Thanks once again! "Harald Staff" wrote: "JE McGimpsey" skrev i melding ... In article , "Harald Staff" wrote: You have to know a little theory on this. 1 is a day in excel. Which makes 0.5 half a day, 12 hours, and 8:30 the same as 1/3. Starting the day at 00:30, Harald? <g Doh!!! 1/3 = 8:00 Sorry & thanks. Best wishes Harald |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
entering numbers to display a time format | Excel Discussion (Misc queries) | |||
Help - Information with time and date | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
Combined date time cell to separate date & time components | Excel Discussion (Misc queries) | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions |