Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time formats
when working out a time sheet for example, i want to be able to enter time
using a decimal point (.) instead of a colan(:) and be able to add them together to get decimal hours. ie. start 8.00 (8:00) finish 3.45 (15:45) minus .30minutes (0:30) = 7.25 (7:15) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time formats
To convert hh.mm in a1 to an Excel time, use:
=time(int(a1),mod(a1,1)*100,0) If you want to enter a time in a2 with an assumed pm, use: =time(int(a2)+12,mod(a2,1)*100,0) Your requested formula would then be: =a2-a1-time(0,30,0) Regards, Fred. "adhide" wrote in message ... when working out a time sheet for example, i want to be able to enter time using a decimal point (.) instead of a colan(:) and be able to add them together to get decimal hours. ie. start 8.00 (8:00) finish 3.45 (15:45) minus .30minutes (0:30) = 7.25 (7:15) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time formats
if the assumed time is not PM format?
"Fred Smith" wrote: To convert hh.mm in a1 to an Excel time, use: =time(int(a1),mod(a1,1)*100,0) If you want to enter a time in a2 with an assumed pm, use: =time(int(a2)+12,mod(a2,1)*100,0) Your requested formula would then be: =a2-a1-time(0,30,0) Regards, Fred. "adhide" wrote in message ... when working out a time sheet for example, i want to be able to enter time using a decimal point (.) instead of a colan(:) and be able to add them together to get decimal hours. ie. start 8.00 (8:00) finish 3.45 (15:45) minus .30minutes (0:30) = 7.25 (7:15) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time formats
IF you want to enter time in decimal then enter like this:
Start time in A1: 8 Finish time in B1: 15.75 (not 3.45) =B1-A1-0.5 "adhide" wrote: when working out a time sheet for example, i want to be able to enter time using a decimal point (.) instead of a colan(:) and be able to add them together to get decimal hours. ie. start 8.00 (8:00) finish 3.45 (15:45) minus .30minutes (0:30) = 7.25 (7:15) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time formats
Then don't add 12.
Regards, Fred. "adhide" wrote in message ... if the assumed time is not PM format? "Fred Smith" wrote: To convert hh.mm in a1 to an Excel time, use: =time(int(a1),mod(a1,1)*100,0) If you want to enter a time in a2 with an assumed pm, use: =time(int(a2)+12,mod(a2,1)*100,0) Your requested formula would then be: =a2-a1-time(0,30,0) Regards, Fred. "adhide" wrote in message ... when working out a time sheet for example, i want to be able to enter time using a decimal point (.) instead of a colan(:) and be able to add them together to get decimal hours. ie. start 8.00 (8:00) finish 3.45 (15:45) minus .30minutes (0:30) = 7.25 (7:15) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
time formats
Heads up:
When enteing time durations as hundreths of an hour you don't experience different times for every hundreth of an hour. For example, entering .24 or .23 will both display as 14 minutes. This is rounding issue. There are a few others. "adhide" wrote: when working out a time sheet for example, i want to be able to enter time using a decimal point (.) instead of a colan(:) and be able to add them together to get decimal hours. ie. start 8.00 (8:00) finish 3.45 (15:45) minus .30minutes (0:30) = 7.25 (7:15) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
time formats | Excel Worksheet Functions | |||
Converting time formats into actual time(minutes) | Excel Discussion (Misc queries) | |||
Help with time formats | Excel Worksheet Functions | |||
Time Formats | Excel Discussion (Misc queries) | |||
Time Formats | Excel Discussion (Misc queries) |