Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

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.

View Answers     

Similar Excel Tutorials

How to Find and Understand Excel Functions
In this tutorial I am going to show you how to find and understand new Excel Functions. If you go to the Formulas t ...
MODE() - Find Most Repeated Value in a List in Excel
The MODE() function in Excel allows you to quickly find the most repeated or frequently occuring value within a lis ...
How to Use Multiple Functions and Formulas in a Single Cell in Excel
Lets learn how to put multiple functions and formulas in a single cell in Excel in order to build more complex form ...
Dynamic Formulas that Update When you Add Data in Excel
I'll show you how to make formulas and functions that automatically update when more data is added to a range in E ...

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.


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


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:


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?



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?


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

For exemple:

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.


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


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

$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

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.


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 >>" target="_blank"> Excel Jeanie HTML 4


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

and so on...


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?

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:

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!


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:


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:

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


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
=MROUND(H4/25.4, 1/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?

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.

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


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


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.




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!

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:

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

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!