Time In Decimals

[Superslinky]

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

 

[Harald Staff]

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

 

[Ravinder S. Mahoon]

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.

 

[Harald Staff]

Hi

=CEILING(A1*24,0.5)
where A1 is the time, replace it with your actual calculation.

HTH. Best wishes Harald

 

[JE McGimpsey]

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>

 

[Harald Staff]

Starting the day at 00:30, Harald? <g>

Doh!!!
1/3 = 8:00
Sorry & thanks.

Best wishes Harald

 

[constance]

Hi

=CEILING(A1*24,0.5)
where A1 is the time, replace it with your actual calculation.

HTH. Best wishes Harald




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

 

[Ravinder S. Mahoon]

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!