Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Start, Finish including break time
Hi all,
I currently have a excel spreadsheet set up to take the following. Just an example: Cell A1 - Time Started (In 24 hour time) - "06:30" Cell A2 - Break Time (Generally Half an hour) - "00:30" Cell A3 - Finish Time - "16:30" Total - =(A3-A2-A1)*24 Which gives me a result in hours which I wanted. "9.5" However When entering in the break time I want to be able to enter it in as ..5 (for half and hour) instead of having to enter in 00:30 for 30 minutes and still calculate the correct response at the end. Also, i'd like to be able to enter the times in without having to enter the : in, for example "0630" (starting at 06:30) and "1630" (finishing at 16:30). Could someone please let me know whether the above is possible, and if maybe you have some suggestions for cleaning it up it would be appreciated. Regards, Ricardo |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Start, Finish including break time
Chip Pearson gives two macros to do that for dates and times he
http://www.cpearson.com/excel/DateTimeEntry.htm Hope this helps. Pete On Aug 25, 8:46*am, Ricardo wrote: Hi all, I currently have a excel spreadsheet set up to take the following. Just an example: Cell A1 - Time Started (In 24 hour time) - "06:30" Cell A2 - Break Time (Generally Half an hour) - "00:30" Cell A3 - Finish Time - "16:30" Total - =(A3-A2-A1)*24 Which gives me a result in hours which I wanted. "9.5" However When entering in the break time I want to be able to enter it in as .5 (for half and hour) instead of having to enter in 00:30 for 30 minutes and still calculate the correct response at the end. Also, i'd like to be able to enter the times in without having to enter the : in, for example "0630" (starting at 06:30) and "1630" (finishing at 16:30). Could someone please let me know whether the above is possible, and if maybe you have some suggestions for cleaning it up it would be appreciated. Regards, Ricardo |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Start, Finish including break time
Format A1 and A3 as TEXT
=TEXT(A3-A1,"00\:00")*24-A2 "Ricardo" wrote: Hi all, I currently have a excel spreadsheet set up to take the following. Just an example: Cell A1 - Time Started (In 24 hour time) - "06:30" Cell A2 - Break Time (Generally Half an hour) - "00:30" Cell A3 - Finish Time - "16:30" Total - =(A3-A2-A1)*24 Which gives me a result in hours which I wanted. "9.5" However When entering in the break time I want to be able to enter it in as .5 (for half and hour) instead of having to enter in 00:30 for 30 minutes and still calculate the correct response at the end. Also, i'd like to be able to enter the times in without having to enter the : in, for example "0630" (starting at 06:30) and "1630" (finishing at 16:30). Could someone please let me know whether the above is possible, and if maybe you have some suggestions for cleaning it up it would be appreciated. Regards, Ricardo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
start finish dates | Excel Discussion (Misc queries) | |||
Calculate working hours from a start and finish time over several | Excel Worksheet Functions | |||
Start & Finish Time | Excel Worksheet Functions | |||
how to calculate time start & time finish in quarter hour | Excel Discussion (Misc queries) | |||
template or formula for start time -finish time -total hours ple | New Users to Excel |