Excel-help Please

[thaigerd]

Hi,

Need some help please for Excel:

I am working at the moment at an Excel sheet which has some cells with a time.

I would like to have a formula where it shows in another cell a new time(minus 24 hours)

Or: Is it possible to create a cell with date and time and than a new cell which calculates a new date and time minus 24 hrs?

Thanks for any help!

Gerd

[PeaceBlondie]

Along the top row, there is a tab for "Format," but that breaks down into two choices: Date, Time (and many others). You can choose a date format for all the cells you wish, such as 06/16/01, in any order that you choose.

In cell A1, I input 06/16/01 and entered.

Then in cell A2, I input =A1-1 and entered. The result was 06/15/01

Of course, when you get past the =sign, you can use your direction pointers and just move to the cell you want. In other words, =move-1 and enter.

You could copy the formula from A2 horizontally, vertically, or anywhere else by reference.

Does that help? I'll play with the times functon now.

[One hour later:]No, I couldn't figure out the formula for time of day.

[thaigerd]

Along the top row, there is a tab for "Format," but that breaks down into two choices: Date, Time (and many others). You can choose a date format for all the cells you wish, such as 06/16/01, in any order that you choose.

In cell A1, I input 06/16/01 and entered.

Then in cell A2, I input =A1-1 and entered. The result was 06/15/01

Of course, when you get past the =sign, you can use your direction pointers and just move to the cell you want. In other words, =move-1 and enter.

You could copy the formula from A2 horizontally, vertically, or anywhere else by reference.

Does that help? I'll play with the times functon now.

[One hour later:]No, I couldn't figure out the formula for time of day.

Thanks PB,

at the moment I am "playing" with that formula tools and try to find a good solution.

My problem is:

I want to make a sheet for a food production(I am a Chef). It means for a product which we are delivering tomorrow has a strict time limit according to intern. food safety rules.

In case a product which we are deliver tomorrow at 6pm we are allowed to start production today at 6pm today.

So I need a formula which can calculate date and time.

Have a nice day!

Gerd

[Crossy]

Try this

serving_time.xls

Input serving time in D4

Input preparation time shift eg 22 hours in B4 (decimal hours eg 15hours 30mins = 15.5 hours)

Cell F4 is the one of interest, shows the earliest date / time you can prepare your dish

EDIT Forgot to say, the two cells with date and time in need to be formatted with the 'custom' number format "dd/mm/yyyy hh:mm" in order to display correctly

Edited June 16, 2006 by Crossy

[a269652]

I would like to have a formula where it shows in another cell a new time(minus 24 hours)

Or: Is it possible to create a cell with date and time and than a new cell which calculates a new date and time minus 24 hrs?

Yes it is, I'll post a solution when I get a bit more time, unless someone else beats me to it.

[thaigerd]

Great help !!!

Thank you all,

Gerd

[thaigerd]

Try this

serving_time.xls

Input serving time in D4

Input preparation time shift eg 22 hours in B4 (decimal hours eg 15hours 30mins = 15.5 hours)

Cell F4 is the one of interest, shows the earliest date / time you can prepare your dish

EDIT Forgot to say, the two cells with date and time in need to be formatted with the 'custom' number format "dd/mm/yyyy hh:mm" in order to display correctly

Thanks Crossy

Your formula works very well in my sheet! Only a little problem left:

With this sheet I am creating I want to give my staff an easy tool to find out the correct time for the food production(and for me a tool the check their work ).

In some cases it appears that the production time is out of our working hours. What means all times after 15:00 are not feasible, so we have to produce the next day at 8:00.

Is there a way to change this formula? Let's say always when the calculated time is after 15:00

it converts automatic to the next day 8:00 ?

Sorry when bothering you to much but your first formula was that good

Of course I own you a dinner or something else when you come down to Phuket.

Thank you,

Gerd

[zoltannyc]

Gerd,

I added three columns to Crossy's solution, Workday start, Workday end and Earliest workday prep time. I guess is self explanatory I left the other staff from Crossy to see it better how was it changed.

Now every row works as a separate little application:

1. enter the timeshift (you mentioned that the default is 24hour)

2. enter the start of workday and the end (note both 1 and 2 are in decimals not hours e.g. 8:30am should be 8.50)

3. enter the serving time

4. copy the formulas for column E, F from the prev. row.

in E you will see the normal calculation in F you will see the workday adjusted value.

hope helps.....

foodprep.xls

[thaigerd]

Gerd,

I added three columns to Crossy's solution, Workday start, Workday end and Earliest workday prep time. I guess is self explanatory I left the other staff from Crossy to see it better how was it changed.

Now every row works as a separate little application:

1. enter the timeshift (you mentioned that the default is 24hour)

2. enter the start of workday and the end (note both 1 and 2 are in decimals not hours e.g. 8:30am should be 8.50)

3. enter the serving time

4. copy the formulas for column E, F from the prev. row.

in E you will see the normal calculation in F you will see the workday adjusted value.

hope helps.....

Thanks Zoltannyc

At the weekend now I will make a new sheet using your version and as soon as I am finished I will let you know.

May be interesting for you to know:

I work as a chef and I feel very obligated to our customers and consumers to deliver a highest possible quality at all the times. So I have to follow HACCP (an international control and monitoring system for food production) what makes sure hygienic and clean food products.

Thanks again!

Gerd

[Crossy]

Gerd,

I added three columns to Crossy's solution, Workday start, Workday end and Earliest workday prep time. I guess is self explanatory I left the other staff from Crossy to see it better how was it changed.

Now every row works as a separate little application:

1. enter the timeshift (you mentioned that the default is 24hour)

2. enter the start of workday and the end (note both 1 and 2 are in decimals not hours e.g. 8:30am should be 8.50)

3. enter the serving time

4. copy the formulas for column E, F from the prev. row.

in E you will see the normal calculation in F you will see the workday adjusted value.

hope helps.....

Look at that, I turn my back for five minutes and someone does it for me :)

Looks pretty good to me zoltannyc, cheers.

[thaigerd]

It nearly works-but some minor problems

I do not want to publish my sheet here in a public forum cause there are some company names and numbers in it.

May be you send me an email to [email protected]

and I reply with my sheet to explain you my little problem. It's easier to understand when you see that sheet.

Thanks,

Gerd

[gharknes]

It nearly works-but some minor problems

I do not want to publish my sheet here in a public forum cause there are some company names and numbers in it.

May be you send me an email to [email protected]

and I reply with my sheet to explain you my little problem. It's easier to understand when you see that sheet.

Thanks,

Gerd

in Excel dates can be represented with numbers that you can work with in "work cells" then display the results in the cells you want to see them as date and time, so have a work area on your spreadsheet and a view area. Go to rght click format cells then custom, select date and time, then type a number like 12 into the cell it will be displayed as a date and time.