Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Shaun
 
Posts: n/a
Default How do I assign a numeric value to a text letter

I have a rota worksheet that contains columns for start & finish times each
weekday and a final column that adds the total hours worked. I would like to
be able to enter text such as Holiday or Training in the daily columns but it
causes an error msg in the total hours cell.

I would like to asssign a value to the text, eg HOL would be considered as a
normal day - 8 hrs - and included in the total hrs.
  #2   Report Post  
Peter
 
Posts: n/a
Default

I've solved a similar problem by having 2 columns for each daily column. The
first column is the "type" of work eg: NORM or HOL etc. The second column
records the relevant "hours" eg: 8, 4, 0 etc. The total column only add the
hours columns. You can create a lookup table so that when you put HOL in the
"type" column then 8 (for example) is shown in the hours column.

HTH
--
Peter
London, UK


"Shaun" wrote:

I have a rota worksheet that contains columns for start & finish times each
weekday and a final column that adds the total hours worked. I would like to
be able to enter text such as Holiday or Training in the daily columns but it
causes an error msg in the total hours cell.

I would like to asssign a value to the text, eg HOL would be considered as a
normal day - 8 hrs - and included in the total hrs.

  #3   Report Post  
Sandy Mann
 
Posts: n/a
Default

You don't mention excluding unpaid meal breaks so with times entered as XL
Times try:

=SUM(B2,D2,F2,H2,J2)-SUM(A2,C2,E2,G2,I2)+COUNTIF(A2:J2,"*")/6

The SUM will ignore text and, provided the text is in pairs, it will add 8
hours.
(One BIG failing is that with incorrect dtat a figure will still be returned
and errors willl not be highlighted by #ERROR!)

I therefore don't say that it is a good solution. I can't help but feel
that you would be better re-desigining your spreadsheet.

Sandy

Replace@mailinator with @tiscali.co.uk

"Shaun" wrote in message
...
I have a rota worksheet that contains columns for start & finish times each
weekday and a final column that adds the total hours worked. I would like
to
be able to enter text such as Holiday or Training in the daily columns but
it
causes an error msg in the total hours cell.

I would like to asssign a value to the text, eg HOL would be considered as
a
normal day - 8 hrs - and included in the total hrs.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
convert text to numeric vba add-in? sulmau Excel Worksheet Functions 1 July 25th 05 08:41 PM
Autofitting a row Josephine Excel Discussion (Misc queries) 2 March 3rd 05 04:37 PM
Sort or Filter option? Mcobra41 Excel Worksheet Functions 3 February 23rd 05 08:22 PM
Read Text File into Excel Using VBA Willie T Excel Discussion (Misc queries) 13 January 8th 05 01:37 AM
Convert Numeric into Text Monty Excel Worksheet Functions 0 December 18th 04 10:25 PM


All times are GMT +1. The time now is 10:41 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"