Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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.

View Answers     

Similar Excel Tutorials

Easily Enter Fractions into Excel
This is a very simple trick to enter fractions quickly without having to manually format anything. This allows you ...
Get a Date that is So Many Working Days Before or After a Date in Excel - WORKDAY
How to find a date that is so many days before or after a specified date, excluding weekends and any holidays. This ...
Pause a Macro or Make it Slow Down in Excel
How to make a macro stop or pause for a specified amount of time. This allows you to slow down the execution of a m ...
Generate Random Numbers within a Range in Excel
How to generate random whole numbers (integers) that are between two numbers.  This allows you to set a minimum and ...

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
Return the ISO Week Number from a Date in Excel - UDF
- Return the ISO Week Number in Excel with this UDF. This is a simple to use UDF (user defined function) that returns the

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




Hi Guys

I have a set of data stretching to 29000 rows...and growing. this essentially consists of 5 columns date/aircraft type/departure city/arrival city/time.

i need to extact data within a date range (7 day period) across the course of 5 years, i am controlling this by referencing the formular to dates in C2 AND D2.
I am asking sumproduct to deliver all criteria that is equal to or greater than C2 and i then subtract all data which is greater than D2, thus leaving me with a week total.
i do this throughout the formular with 2 additional criteria of A320 and LTN.

=SUMPRODUCT(--(Schedule!$A:$A>=C2),--(Schedule!$B:$B="A320"),--(Schedule!$C:$C="LTN"),Schedule!$E:$E)-SUMPRODUCT(--(Schedule!$A:$A>=D2),--(Schedule!$B:$B="A320"),--(Schedule!$C:$C="LTN"),Schedule!$E:$E)+SUMPRODUCT(--(Schedule!$A:$A>=C2),--(Schedule!$B:$B="A320"),--(Schedule!$D:$D="LTN"),Schedule!$E:$E)-SUMPRODUCT(--(Schedule!$A:$A>=D2),--(Schedule!$B:$B="A320"),--(Schedule!$D:$D="LTN"),Schedule!$E:$E)

I then had to build upon the formula to exclude a couple of additional variables and i ended up with:

=SUMPRODUCT(--(Schedule!$A:$A>=C2),--(Schedule!$B:$B="A320"),--(Schedule!$C:$C="LTN"),Schedule!$E:$E)-SUMPRODUCT(--(Schedule!$A:$A>=D2),--(Schedule!$B:$B="A320"),--(Schedule!$C:$C="LTN"),Schedule!$E:$E)+SUMPRODUCT(--(Schedule!$A:$A>=C2),--(Schedule!$B:$B="A320"),--(Schedule!$D:$D="LTN"),Schedule!$E:$E)-SUMPRODUCT(--(Schedule!$A:$A>=D2),--(Schedule!$B:$B="A320"),--(Schedule!$D:$D="LTN"),Schedule!$E:$E)-SUMPRODUCT(--(Schedule!$A:$A>=C2),--(Schedule!$B:$B="A320"),--(Schedule!$C:$C="LTN"),--(Schedule!$D:$D="LGW"),Schedule!$E:$E)+SUMPRODUCT(--(Schedule!$A:$A>=D2),--(Schedule!$B:$B="A320"),--(Schedule!$C:$C="LTN"),--(Schedule!$D:$D="LGW"),Schedule!$E:$E)-SUMPRODUCT(--(Schedule!$A:$A>=C2),--(Schedule!$B:$B="A320"),--(Schedule!$C:$C="LTN"),--(Schedule!$D:$D="MAN"),Schedule!$E:$E)+SUMPRODUCT(--(Schedule!$A:$A>=D2),--(Schedule!$B:$B="A320"),--(Schedule!$C:$C="LTN"),--(Schedule!$D:$D="MAN"),Schedule!$E:$E)-SUMPRODUCT(--(Schedule!$A:$A>=C2),--(Schedule!$B:$B="A320"),--(Schedule!$C:$C="LTN"),--(Schedule!$D:$D="BHX"),Schedule!$E:$E)+SUMPRODUCT(--(Schedule!$A:$A>=D2),--(Schedule!$B:$B="A320"),--(Schedule!$C:$C="LTN"),--(Schedule!$D:$D="BHX"),Schedule!$E:$E)-SUMPRODUCT(--(Schedule!$A:$A>=C2),--(Schedule!$B:$B="A320"),--(Schedule!$C:$C="LTN"),--(Schedule!$D:$D="EMA"),Schedule!$E:$E)+SUMPRODUCT(--(Schedule!$A:$A>=D2),--(Schedule!$B:$B="A320"),--(Schedule!$C:$C="LTN"),--(Schedule!$D:$D="EMA"),Schedule!$E:$E)-SUMPRODUCT(--(Schedule!$A:$A>=C2),--(Schedule!$B:$B="A320"),--(Schedule!$C:$C="LTN"),--(Schedule!$D:$D="LBA"),Schedule!$E:$E)+SUMPRODUCT(--(Schedule!$A:$A>=D2),--(Schedule!$B:$B="A320"),--(Schedule!$C:$C="LTN"),--(Schedule!$D:$D="LBA"),Schedule!$E:$E)

The formula works fine, but when you consider the need to have 250 odd columns by 24 rows of data, excel grinds to a halt.

I have considered using a separate spreadsheet because in therory it would only require an update once a month and then either linking my master or copying and pasting the values across (so as not to slow down the master document).

i believe the problem is because i have asked the formula to check ALL data >=C2 then again >D2, this is a long winded way to get the final result.

Can anyone suggest an improvement to speed up this process (not pivot please). I am thinking that by creating a more dynamic date range criteria i will be able to have the formula drill down to the few hundred rows of data that i need to extract rather than it looking at all 29000 rows.

many thanks

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 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



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!





I need to show a calculation using a fraction that has not been reduced. So I need to do the following:
A2=435.15
C2=15/30

I need to be able to take A x C = Answer. However, the current situation will return the #VALUE error. If I try reformatting the fraction cell, it calculates it, but changes the 15/30 to 1/2 and the customer needs to see 15/30.
Is there a way to do this?
Thank you!



Hello,

I have a table of data that lists work schedules for the month in columns. There are 155 different schedules in total, and each column simply lists the work site for a given day:

schedules.png


What I am trying to do is write a formula that shows for each schedule (schedule a, schedule b, schedule c, etc) how frequently each person assigned to each schedule will work with each other. I am writing the schedules for my employees, and they have requested to be able to pick their schedules based on who they will work with. In other words, employee 1 and employee 2 want to work together as much as possible, but no two schedules are the same next month.

I've tried creating an array and using a countif function to narrow the result down to certain job sites, but nothing I am doing seems to be working. Any help would be appreciated.

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 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 create a production schedule for multiple projects. I need to input a formula that will take the start date (in the D column) and the number of days a crew will be working on a given project (in the I column) to get an approximate finish date. Obviously the finish date has to take weekends and holidays into consideration. And it's that last part I'm having issues with, specifically the floating holidays like Thanksgiving. The solutions I've seen on this site require having the specific holidays in the sheet with the other data, which I don't want. I'd also like the formula to work for not just this year, but the next and the one after that, etc.

Is it possible to do this? I've attached an example schedule just in case that helps at all.Roofing Production Schedule Help.xlsx

Thanks in advance!

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




Hello,

As part of an Excel workbook for my job, I have created a section whereby a user can input a fraction, as text so it does not automatically convert, and then a formula will convert that fraction to a percentage.

That part of the formula appears to work just fine. However, I am having an issue with the conditional formatting I have applied to the percentage cell, which is supposed to color-code the cell based on the percentage that is calculated.

In the attached example, you can see that the percentage cell has been color-coded properly based on the data that has been entered in the fraction cell.

But if you change this fraction to 25/42, which calculates the percentage as 60%, the cell remains white, without any fill formatting.

Some fractions seem to work fine with the formatting, some do not. I have no idea why it likes some and why it doesn't like others.

Please advise as to how this can be fixed so that every fraction that falls within the established percentages shows up color-coded for ease of use.

Thank you!WorkExample1.xlsx



Hi Guys

Ok, so i have developed a formula to work out a delay time from a complex data set.

Problem i am having is that the result is being displayed as a fraction of a day, for example, if a delay time was of 1 minute, it would display as -0.000694444

Does anyone know how to change the format of the cell to show this as 1 (for 1 minute) rather than fraction of a day... or... a mathematical formula to calculate that into minutes?

Thank you


PS - Usually browse these forums for answers, but stuck with this, so registered - keep up the excellent work!

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


Can anybody please assist in this
How do I get a fraction to show as a fraction rather than decimal in the formula bar without formatting the cell as text.
I have done this before but cannot remember how I achieved it.
Thanks in advance
Gary


Hi,
I know how to use the fraction format, but it displays the fraction as, for example,11 3/4, where the fraction (3/4) is displayed as the same font size as the integer, 11. What I want is for the fraction, 3/4, to be smaller font than the integer, 11, AND that the 3 be above the 4 not on the same horizontal level. I can't seem to show it in this text editor, but if you go into MS Word and type 11 space 3/4 you'll get exactly what I want, but its in Word not Excel. When I copy/paste the text from Word to Excel it display exactly as I want it BUT the 3/4 is inserted as a symbol not as text or a number.
Any help is appreciated




Hello,

I need help with a formula that will reduce the number within a cell by a fraction each day, that is, I would like to enter 1/4 in a cell and have it reduce by 1/128 (or any number I select) each day. I would like the same effect you get from a date countdown formula only this is for fractions.

Thanks

Hi all - any help would be most appreciated. I am using the following formula to check a range for the letter D and then to add how many ranges have "D's" - This works fine, but if I copy and paste the formula to another cell and change the criteria to "N" instead of "D" and then insert "N's" in the range nothing happens???? Any idea why please and how I can fix this? Formula I am using is

=SUM(IF('Shift Schedule'!C6:BS6="D",1,0)+IF('Shift Schedule'!C22:BS22="D",1,0)+IF('Shift Schedule'!C38:BS38="D",1,0)+IF('Shift Schedule'!C54:BS54="D",1,0)+IF('Shift Schedule'!C70:BS70="D",1,0)+IF('Shift Schedule'!C86:BS86="D",1,0)+IF('Shift Schedule'!C102:BS102="D",1,0))

I know this is a bit messy, but I am still learning nested formula and I haven't quite got it yet! Thanks


Hi I am wanting to do a project that will make my life alittle easier when putting my work schedule into my calender on outlook. Currently my employer using just pencil and paper to do our schedules. What I am trying to do is place the schedule into a excel document that will then be translated into another so that outlook will be able to understand it. This is what the schedules look like when they come out....

Name Day1 Day2 Day3 Day4.....
Employe1 A A Q D
Employe2 D M D E

Where each Day is a specific date in the schedule like 4/25/10, each employe is a different person that works, and the letters like "A, D, Q, M" are all shifts that have hours associated with them.

What I was wanting to do is make a way to where I put the schedule into a excel spreadsheet and then when I'm done it will translate each persons schedule i.e. each different row, into different spreadsheets that is able for outlook to read.

Thanks




Hi all,

I have a spreadsheet that will return a fraction using

=COUNTIF(V6:V16,">2")/COUNTIF(V6:V16,">0")

Unfortunately when I want it to return 9/9 it doesn't, it returns 1.

Is there a work around to display the fraction 9/9?

I can't edit the denominator in format cells as this varies.

All help gratefully received
Thanks in advance