Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate hours from 10:00-21:00 format
Hello!
mr Somebody has a several worksheets wich includes lots of cells which has start and end hours in format 10:00-18:00, 13:15-21:15 etc... Cells are formatted as text. He asked me if it's possible to calculate hours between start and end hours automaticly. I check out macros and notice that java, php and few other programming languages does not help me now. I notice that if end hour is before start hour calculation works but not vice versa. How can i split string into two pieces and calculate it to other cell. Let's say cell X includes string "10:00-20:00", I split it to Array whics becomes 2 dimension arr and then calc cell Y value = Array2-Array1. Is there function to evaluate text cells??? I think this is possible but how???????? Advanced Super Thanx -jori luoto |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate hours from 10:00-21:00 format
Hi
why not split these values in two columns. Use 'Data - Text to columns' for this. Afterwards simply subtract the values with a formula like =B1-A1 and if your time values can span midnight use =B1-A1+(B1<A1) -- Regards Frank Kabel Frankfurt, Germany Jorch wrote: Hello! mr Somebody has a several worksheets wich includes lots of cells which has start and end hours in format 10:00-18:00, 13:15-21:15 etc... Cells are formatted as text. He asked me if it's possible to calculate hours between start and end hours automaticly. I check out macros and notice that java, php and few other programming languages does not help me now. I notice that if end hour is before start hour calculation works but not vice versa. How can i split string into two pieces and calculate it to other cell. Let's say cell X includes string "10:00-20:00", I split it to Array whics becomes 2 dimension arr and then calc cell Y value = Array2-Array1. Is there function to evaluate text cells??? I think this is possible but how???????? Advanced Super Thanx -jori luoto |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate hours from 10:00-21:00 format
Hi Frank,
a better formula (IMO) is =MOD(B1-A1,1) One for your library? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Frank Kabel" wrote in message ... Hi why not split these values in two columns. Use 'Data - Text to columns' for this. Afterwards simply subtract the values with a formula like =B1-A1 and if your time values can span midnight use =B1-A1+(B1<A1) -- Regards Frank Kabel Frankfurt, Germany Jorch wrote: Hello! mr Somebody has a several worksheets wich includes lots of cells which has start and end hours in format 10:00-18:00, 13:15-21:15 etc... Cells are formatted as text. He asked me if it's possible to calculate hours between start and end hours automaticly. I check out macros and notice that java, php and few other programming languages does not help me now. I notice that if end hour is before start hour calculation works but not vice versa. How can i split string into two pieces and calculate it to other cell. Let's say cell X includes string "10:00-20:00", I split it to Array whics becomes 2 dimension arr and then calc cell Y value = Array2-Array1. Is there function to evaluate text cells??? I think this is possible but how???????? Advanced Super Thanx -jori luoto |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate hours from 10:00-21:00 format
Bob
neat :-) -- Regards Frank Kabel Frankfurt, Germany Bob Phillips wrote: Hi Frank, a better formula (IMO) is =MOD(B1-A1,1) One for your library? "Frank Kabel" wrote in message ... Hi why not split these values in two columns. Use 'Data - Text to columns' for this. Afterwards simply subtract the values with a formula like =B1-A1 and if your time values can span midnight use =B1-A1+(B1<A1) -- Regards Frank Kabel Frankfurt, Germany Jorch wrote: Hello! mr Somebody has a several worksheets wich includes lots of cells which has start and end hours in format 10:00-18:00, 13:15-21:15 etc... Cells are formatted as text. He asked me if it's possible to calculate hours between start and end hours automaticly. I check out macros and notice that java, php and few other programming languages does not help me now. I notice that if end hour is before start hour calculation works but not vice versa. How can i split string into two pieces and calculate it to other cell. Let's say cell X includes string "10:00-20:00", I split it to Array whics becomes 2 dimension arr and then calc cell Y value = Array2-Array1. Is there function to evaluate text cells??? I think this is possible but how???????? Advanced Super Thanx -jori luoto |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate hours from 10:00-21:00 format
Thanx both of you... that seems to be working fine.
That was kind of thing i was searching. -jori On Sun, 30 May 2004 16:15:31 +0200, "Frank Kabel" wrote: Hi why not split these values in two columns. Use 'Data - Text to columns' for this. Afterwards simply subtract the values with a formula like =B1-A1 and if your time values can span midnight use =B1-A1+(B1<A1) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate total time(hours/minutes) from text/custom format column | Excel Discussion (Misc queries) | |||
How do I format a cell to calculate overnight Hours? | Excel Discussion (Misc queries) | |||
How can I calculate hours into pay...in this format hh:mm:ss | Excel Discussion (Misc queries) | |||
Format Cells to calculate hours and minutes taken to complete task | Excel Worksheet Functions | |||
Drivers Hours Timesheet - Calculate Hours Worked on Weekly Basis | Excel Discussion (Misc queries) |