Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Workday Function For A Fraction Of A Day

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I am creating a "schedule" on excel at work and some of the processes take half a day or 1/4 of a day. so how do i write my formula to make sure sure that my next process date will be correct on my schedule. Currently any fraction of a day is considered as a day.


Similar Excel Video Tutorials

Helpful Excel Macros

Format Cells as a Fraction in Excel Number Formatting
- This free Excel macro will automatically format a selected cell or many selected cells in the Fraction number format in
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Calculate the Future Value (FV) of Compound Interest in Excel - UDF Macro
- This UDF (user defined function) calculates the Future Value of Compound Interest in Excel. The mathematical formula th
Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display

Similar Topics







I am creating a "schedule" on excel at work and some of the processes take 0.5 days or 0.25 days. so how do i write my formula to make sure that my next process date will be correct on my schedule.
I am currently using workday function but when the number of days is a fraction it doesn't calculate correctly whether it's .25 days or .5 days. Excel will assume these to be a whole day


I have the following code from a model

ROUTING_DECISION 31 NAME "St Johns Way PC" LABEL 0.00 0.00
LINK 45 AT 110.523
TIME FROM 0.0000 UNTIL 900.0000 FROM 900.0000 UNTIL 1800.0000 FROM 1800.0000 UNTIL 2700.0000 FROM 2700.0000 UNTIL 3600.0000 FROM 3600.0000 UNTIL 4500.0000
FROM 4500.0000 UNTIL 5400.0000 FROM 5400.0000 UNTIL 6300.0000 FROM 6300.0000 UNTIL 7200.0000
VEHICLE_CLASSES 60
ROUTE 5 DESTINATION LINK 14 AT 79.972
FRACTION 0.000 FRACTION 0.000 FRACTION 0.000 FRACTION 0.000 FRACTION 0.000
FRACTION 0.000 FRACTION 0.000 FRACTION 0.000
OVER 10038 13 10094 4 10011 5 10025 78 10072 47
10024 34 10033 6 10006 10 10095 9 10010
ROUTE 4 DESTINATION LINK 56 AT 103.836
FRACTION 0.000 FRACTION 0.000 FRACTION 0.000 FRACTION 0.000 FRACTION 0.000
FRACTION 0.000 FRACTION 0.000 FRACTION 0.000
OVER 10038 13 10094 4 10011 5 10025 78 10072 47
10024 34 10002 6 10004 8 10051
ROUTE 3 DESTINATION LINK 7 AT 96.900
FRACTION 0.000 FRACTION 0.000 FRACTION 0.000 FRACTION 0.000 FRACTION 0.000
FRACTION 0.000 FRACTION 0.000 FRACTION 0.000
OVER 10038 13 10094 4 10011 5 10007 78 10072 47
10042 57 10003
ROUTE 2 DESTINATION LINK 46 AT 101.293
FRACTION 1.000 FRACTION 3.000 FRACTION 3.000 FRACTION 0.000 FRACTION 3.000
FRACTION 1.000 FRACTION 2.000 FRACTION 2.000
OVER 10038 13 10094 4 10011 5 10041 22 10039
ROUTE 1 DESTINATION LINK 20 AT 51.158
FRACTION 0.000 FRACTION 1.000 FRACTION 0.000 FRACTION 0.000 FRACTION 0.000
FRACTION 0.000 FRACTION 0.000 FRACTION 0.000
OVER 10038 13 10014 4 10013 12 10032


This sequence is then repeated many many times!
I want to take the values after "FRACTION" and replace them with different values and then convert back to text.
My first problem is that the row with Fraction is sometimes over one line and sometimes over 2 lines.

I wanted to insert an identifier column, which would include the ROUTING_DECSION number and the ROUTE number. For each ROUTING_DECSION there will be more than one ROUTE. I have no idea where to start with a formula for this, other than a really complicated IF function. But I dont know if I should use MATCH (and not very good at it!)
Any pointers or ideas would be most welcome.

I waas thinking that a macro may be the way to go, but I have no idea here to start!
Thanks
Janni


I am joining two columns of data.
first col. has size in fraction format: 7 3/8

second column has text string:
HABITAT CINCY POD HAT ERA BLK/RD

Concatenate=
HABITAT CINCY POD HAT ERA BLK/RD 7.375

Changing fraction to decimal, needs to remain fraction.

I have played w/ the formatting to make Number w/ zero decimal places and then selected Fraction, but no luck there.

Any ideas?

thx,

ds


Hey guys, this is my first post on here. I'm hoping somebody might be able to help me solve this problem I am having at work.

I use excel to display ratios in a fraction format...i.e. 12/365, 1/5, 35/255, or however a fraction ends up appearing when reduced to the lowest terms. I'm wondering if there is a way to format a cell to display a fraction so that the numerator is always 1. For example, 2/3 would be 1/1.5, or 23/165 would be 1/7.1739, or 5/13 would be 1/2.6. Can anybody help me write some kind of formula or some other way to format cells to display fractions in that manner?

Thanks!


I'd like to find all the fraction that give 7/24 rapidly in Excel.

For exemple:
2/3*7/16=7/24
1/4*7/6=7/24

7/24 is just for an exemple, it could be any fraction.

So the main goal wuold be to input a fraction and the spreadsheet gives all the possibility of multiplication with two fractions.

Regards,


I have a spreadsheet with columns of fractions, but when you select a cell
the function bar shows a decimal instead and when you save as text file it
saves as decimal instead of fraction. Could someone please tell me how to
make it show as a fraction in the function bar? I have tried formatting the
cell as fraction and it does not work that way

Dani



I have this drill for 3rd & 4th grade students to sharpen their fraction skills. There will be about 50 fraction problems. Is there an easy way to have a wave (.wav) sound play when a check mark shows in column G indicating a correct answer? The b is actually a "Marlet Font" check mark.

Thanks JJ in SD

******** ******************** ************************************************************************> Microsoft Excel - Fraction Test.xls ___Running: xl2000 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout C2 F2 E6 G6 E7 G7 E8 G8 E9 G9 E10 G10 =
A B C D E F G H 1 * * * * * * * * 2 * * 80.0% Pass * 3 * * * 4 My*Fraction * Your**Fraction * Your*Answer Correct*Answer * * 5 * * * * 6 **3/4* + **3/4* = 1*1/2 1*1/2 b * 7 **7/8* + **7/8* = 1*3/4 1*3/4 b * 8 **1/3* + **5/6* = 1*1/6 1*1/6 b * 9 **2/5* + **5/6* = 1*1/4 1*1/4 b * 10 **3/4* + * = *3/4 1*5/8 *
* Sheet1 *
[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


Is there anyway to format a fraction so that the whole number stays regular
size and the actual fraction formats so that they is one number on top of the
other (like in Word). Write now my fraction looks like this in the cell, 2
7/8. I would like the 7/8 to be smaller and more vertical. Does anybody
know how to do this? Any help would be appreciated.



Hello all, newbie here and have been pulling my hair out for a few days. I'm a building official and I am creating a handout on a CD to give to my builders for calculating their permit fees. The permit fees are based of a valuation of the structure and then if the valuation is within certain variables then the permit is a certain fee. Here is the table of fees. I want to be able to come up with a formula that will calculate the fee if a certain cell has the valuation in it.

I take the square feet of living area X a per square foot cost + the squre footage of all garages and porches X a per square foot cost at 80% and that gives me the valuation of the structure. Here is that formula.
=SUM(B16*78.35+D16*31.46)*0.8
This formula gives me a valuation of say.......$146,575 which would calculate to a permit fee of $1256.95. Like I stated I would like excel to figure this for me. Like IF(H16>100,000...<500,000) M13=993.75+5.60per each 1000 or fraction above 100,000.

Any way possible?????
Mule


TOTAL VALUATION FEE
$1.00 to $500.00 $23.50

$501.00 to $2,000.00 $23.50 for the first $500.00 plus $3.05 for each additional $100.00, or fraction thereof, to and including $2,000.00

$2,001.00 to $25,000.00 $69.25 for the first $2,000.00 plus $14.00 for each $1,000.00, or fraction thereof, to and including $25,000.00

$25,001.00 to $50,000.00 $391.75 for the first $25,000.00 plus $10.10 for each additional $1,000.00, or fraction thereof, to and including $50,000.00

$50,001.00 to $100,000.00 $643.75 for the first $50,000.00 plus $7.00 for each additional $1,000.00, or fraction thereof, to and including $100,000.00

$100,001.00 to $500,000.00 $993.75 for the first $100,000.00 plus $5.60 for each additional $1,000.00, or fraction thereof, to and including $500,000.00

$500,001.00 to $1,000,000.00 $3,233.75 for the first $500,000.00 plus $4.75 for each additional $1,000.00, or fraction thereof, to and including $1,000,000.00

$1000,001.00 and up $5,608.75 for the first $1,000,000.00 plus $3.65 for each additional $1,000.00, or fraction thereof


Greetings All,

This is my first thread here, so I hope I am doing everything right... Basically, I have a spreadsheet showing how long it takes to go through certain processes. Say, Part A takes 2 days in Process A, 3 days in process B, and one day in process C. I have another spreadsheet that tells what day these parts are supposed to enter each of the processes (8/7/10 Part A enters Process A, 8/9/10 Part A enters process B and so on...) as well as the quantity. My goal is to make a more visual representation of where each of these parts are in each process over the whole schedule. So I want a spreadsheet with processes on top and dates on the side. I need a script that can look at the date to see when the process starts, then look at the cell that has the time information (days) and then take the quantity of parts and place it in the spreadsheet on the appropriate day and then copy that part quantity below it a number of times =to the # days it takes to complete the process.

So, if part a takes 2 days in process A and there are 20 pieces to be made starting 8/7/10, I want it to put "20" in the column for process A and in the row of 8/7/10 and 8/8/10. I also want this to be able to look through the rest of the schedule and be able to add up any other parts that would so happen to need to be processed on that day. I know that was a mouthful, but I would appreciate any help. I have attached a excel file that should help visualize this. Thanks.


Hi, i have two worksheets "Raw_Data" and Summary. In Raw_Data i have 5 columns of data, what i after is for either a formula or a macro that will return No of items under each age criteria and $ value for each exception against each team in the summary sheet (see below example), if there are multiple exceptions for a team then they need to be summed up, i also require the absolute value.

Raw_Data

A B C D E 7 Source Code AGE Side Exception ABS 8 SYSTEM 931 500,000.00 500,000.00 500,000.00 9 SYSTEM 931 500,000.00 0.00 10 SYSTEM 1859 500,000.00 500,000.00 500,000.00 11 SYSTEM 1859 500,000.00 0.00 12 SYSTEM 931 600,000.00 500,000.00 500,000.00 13 SYSTEM 931 600,000.00 0.00 14 FRACTION 32 0.24 0.24 0.24 15 FRACTION 320 0.05 0.00 16 FRACTION 316 0.01 0.00 17 FRACTION 228 0.19 0.00 18 FRACTION 225 0.01 0.00 19 FRACTION 137 0.12 0.00 20 FRACTION 82 0.02 0.31 0.31 21 FRACTION 46 0.89 0.00 22 FRACTION 46 1.00 0.00 23 FRACTION 42 0.02 0.00

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Summary

C D E F G H I J K L M N O 58 30 60 90 120 59 Team No. of items Value ($) ABS ($) No. of items Value ($) ABS ($) No. of items Value ($) ABS ($) No. of items Value ($) ABS ($) 60 SYSTEM 0 0.00 0.00 0 0.00 0.00 0 0.00 0.00 3 500,000.00 1,500,000.00 61 FRACTION 1 0.24 0.24 1 0.31 0.31 0 0.00 0.00 0 0.00 0.00

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Let say I have 7/24 as a fraction and I'd like to find 2 fractions that give 7/24 as answer when product is done.

What i need to do is find the multiple of 7 and 24 and make all the combinaition...that's what I do on the paper but any idea how to do it in Excel?

Example:
7/24=7/3*1/8
7/24=7/8*1/3
7/24=7/6*1/4
and so on...

Jasmin


Greetings
I have a combo box which pulls its list from a list on one of many sheets. The list is formatted as fraction, & the fractions show carrectlyof course 1/8, 1/2, etc.
When I go to use the combo box they show correctly, but as soon as I make a selection, the fraction is converted to a number .33333, .5, etc.
How can I get the fraction to stay a fraction?

Nevermind!
As I typed this...
Format the fractions as Text in the sheet & they stay fractions!
Hopefully this will help someone someday...
Thanks anyway!



Hi, I'm (sadly) completely new to this forum and mostly new to Excel.

I need to set up a work schedule for 14 people, who rotate between different clinics each week. I would like to use excel to automate the process, rather than picking and choosing who should go where. There are certain parameters that need to be met (equal number of days off per week, only certain people can work in certain spots).

I know this is elementary, but I really have no idea how to get started, and would appreciate any help! Thanks in advance.

The schedule looks like this:

http://i33.photobucket.com/albums/d9...3/schedule.jpg


Hi everyone!
Im new to the forum but am hoping that someone can help me... I visited a forum a few months ago looking for help to create a formula that helps me to have a home basketball schedule automatically update as each day passes. This updates three different cells the next Opponent, Date of the next game, and time of the next game. It worked really well last year but now when I try to put in the new schedule it doesn't work anymore can you help?

The formula is =INDEX(Schedule!A:B,MATCH(NOW(),Schedule!A:A,1)+1,1)

I can send the workbook to anyone who thinks they can help me with fixing it and its also attached to this post i think.

If you are looking at the work book its cells c7, e7 and g7 and then the schedule is in the schedule tab

Thank you so much!!

Allen


I am attempting to set up a formula inside a workbook that when i enter a number on one sheet it enters the description that corresponds to that number on the sheet also and all this is being pulled from another sheet in the workbook. My question is that is there anyway to make this formula short? My master sheet is 300 columns and my secondary sheet has multiple lines that need the formula. here is an example of what i started doing " =IF(C7='Projects Schedule'!B3,'Projects Schedule'!C3,IF(C7='Projects Schedule'!B4,'Projects Schedule'!C4,IF(C7='Projects Schedule'!B5,'Projects Schedule'!C5,IF(C7='Projects Schedule'!B6,'Projects Schedule'!C6,IF(C7='Projects Schedule'!B7,'Projects Schedule'!C7,IF(C7='Projects Schedule'!B8,'Projects Schedule'!C8,IF(C7='Projects Schedule'!B9,'Projects Schedule'!C9)))))))". This is only about 6 columns worth. I sure hope there is a way to create a shorter formula. Please help me.


I know that if I enter =3/4 in a cell it will give me .75 but is there is formula to convert fractions into decimals or is this the best way to do it? I was also wondering if there is some way to convert the decimal amount to a fraction

for measurement conversions I know that
=B3*25.4 will convert a decimal to mm and so will the formula =CONVERT(E4,"in","mm")

and to convert mm to a decimal a few formula that work are
=CONVERT(H3,"mm","in")
=MROUND(H4/25.4, 1/8)
=H5/25.4
=INT(H6/25.39999918*8)/8


But is there a way to convert a fraction to the decimal and is there a way to convert mm to a fraction instead of the decimal equivalent of the fraction?
or maybe a formula to convert mm to inches but instead of the result being in decimal format it would be in fraction format?


Hello everybody,
I am trying to do a simple thing.
I have a number say 35 in A1 and in B1 I divide it by 2 so i get 17.5
I use format cell number to fraction display which gives me 17 1/2

Desired output :I want the fraction to display as 17 1/2" (where " is the sign for inches)

Question #1
Is there a simple formula to do this

I tried = A1 & " " ".. but is removes the fraction formatting which is not acceptable
and displays 17.5"

I tried Text(A1,"??").. where ?? I am not sure what to enter for fraction display

Any help will be much appreciated

Thanks


Hi I write up job cards that require measurements in fraction form. Usually whenever i want to round off a figure , say 10/16, to 5/8, i right click on the figure and format cell it. However, this can be quite time consuming if there are multiple cells and are of a different breakdown.

What i would like to know if there's a shortcut way where when the 10/16 is in the cell, it automatically rounds off to the nearest fraction.

Thanks in advance.


I need to change a five day workweek to a 6 day workweek. Or change a seven day to a six day.

Can I write a simple formula to link a basic 7 day week to a holiday schedule? I will list all of the Sundays on the Holiday tab.

the current formula I am using on a five day work week is:
=WORKDAY(B11,2,Holidays!$A$1:$A$24)

B11 is a start date and this cell adds 2 days to the start date.
The next cell adds 3 days to the new date.
This is so I can reuse the schedule over and over by entering a new start date.

I could use a simple formula like: =B11+2
but I can't seem to link it to the Holiday tab.

Can someone please help.

thanks, Dan


I am trying to express "time out" from two particular deadlines for a list of dates. Using a simple fraction formula, I can get it to express the difference as a number, but I'd like to both insert text, change the fraction to a single number, and add "From XXXX" at the end for each date.

What I have now...

..........A...............................B...............................C

1.....Date 1......................11/17/07...................=(B3-B1)/7
2.....Date 2......................11/18/07...................=(B4-B2)/7
3.....Deadline 1..................3/09/08
4.....Deadline 2..................3/15/08

The result I get with the outcome formatted as a "Fraction,up to one digit"...

C1= 16 1/7
C2= 16 6/7

The results I would like...

C1= 16 weeks, 1 day(s) until Deadline1
C2= 16 weeks, 6 day(s) until Deadline2

Is it even possible to come up with a universal formula for the C column that would work?? Thanks!


I use a custom format (#??/16) to convert the integer contents of two cells to a fraction so that A1=16, B1= 16 , then target cell (C1) displays 16/16 (or a fraction thereof in sixteenths without the least common denominator kicking in) i.e., not 1.

I need to convert that exact fraction displayed in C1 to text in D1 order to concatenate it with a string in E1 and display the concatenated string in F1.

Ideas?

Audiology


I am using 2007.

I have 10 processes that need to happen each on their own days i.e. 1 process 1 day.
These process happen by inserting (1) into any of cells (A1 - J1) depending on which processes are happening. Not all the cells have to be populated.

The next 10 cells (cells K1-T1) have the workday date inserted through a formula(where I am looking for the help)
The due date for the project is in the last cell (u1)and =(X)

This is where the formula begins

process(A1)(1)=date(K1),process(B1)(1)=date(L1),process(C1)(1)=date(M1) etc.
If (A1) is populated with a (1) then (K1) will have a date inserted.


So working backwards as the last task is done the day before the due date. from (U1) in (T1) I want the date to be (U1)-1 should (J1)have a 1 in it.
If (J1) has a (1) and (I1)has a (1) then( T1) must =(U1)-1 and (S1) must =(U1)-2
If (J1) has nothing and (I1)has a 1 then( T1) must =nothing and (S1) must =(U1)-1.
This must go on through all the processes.

Weekends may not be included so dates shown must reflect Monday to Friday and display the date.

Thanks for the help.


Alright I've used Excell for several years, mainly for spreadsheets at work and have never had a problem with it being locked. However I've been training a new guy at work and somehow when I'm not THERE watching him... he manages to make a cell into a fraction and because the document is locked, I can't fix it properly. I've searched on google and gotten nowhere really, any help is appreciated.

I've tried deleting the cell and inputting the information and it still goes back to being a fraction, I am stumped. Is there a way to convert this back to decimal without having to unlock the document?

The formula I'm using is -1709./107 which comes out to -15.97, but Excell is putting it as "-15 69/71" which is still the correct number, but it ruins my spreadsheet entirely because other cells are linked to it. I've also tried only entering -15.97 but Excell still displays the fraction, getting rather annoying.

Not sure how when the cell is locked that this guy can get it to do this...


Hello everyone!
In col. A I have: ER 32 Collet 1/16, so in col. B i type in =right(a1,6) hit enter (I make sure the row is formated for Fractions) then it shows 1/16, I double click the little plus sign so it will populate all of the rows below with the same formats, then copy (col is already highlighted), paste special, value. Then in col. c I format the enter col so it will show numbers (4). I then put in the =b1, enter and I'm expecting the decimal eqv. to show up .0625 but it doesn't it just shows 1/16. Started playing around with it (trying different things) and then I went into the formaula box of B1 and hit the space bar and then enter, then col. c2 showed the decimal eqv. like it was suppose to.
How can I get col. B to show real fraction without having to go to the formula bar each time and put a space before the fraction each time in order to Col. C to show the proper results, and I do make sure each col (B is fraction, and C is number(4)).
Thanks for your help!

Dan