Help with two formulas

[John]

Hi. Just wondered if someone brainy can help me with two formulas? The
first one I was wanting to enter todays date in a cell i.e. =today()

I also would like to have the date of my birthday next year 08/01/2006
in another cell.

I then want to take away todays date from that of my birthday and have
it formatted so it gives the number of days to go until my birthday.
So each different day I open the spreadsheet it will say one day less.
Maybe there is something that can be done with some sort of Count
query? Not being a wizz with formulas though I'm not sure the best way
to do this one.

The second formula I wish to do is a conversion of fuel consumption.
In the UK we calculate our fuel economy in MPG Miles per Gallon. In
Europe they like to do it a new metric way in litres needed for every
100 Km which nobody understands or can relate to.

A lot of car manufacturers are now just using the Euro way so it is a
pain to work out what the fuel consumption figures are for different
car models.

So if a car needs 7 litres of fuel for every 100 Km, I would like to
work out with a formula how many Miles per gallon consumption that
would equate to. Here in the UK it is 4.55 litres to every gallon. I'd
like to have it so I can enter the number in a cell and the next cell
it works it out in. I'm not precisely sure how many KM there are in a
Mile, but I'm sure someone in here might know?

Appreciate any help with these formulas

Cheers

John

 

[Guest]

First setup the formatting of three cells:
1. select A1 and pull-down: Format > Cells.. > Number > Date
and then select a universal format like "14-mar-2001"

2. repeat above for cell A2

3. select A3 and format as General

Put =TODAY() in A1
Put your next birthday in A2
Put =A2-A1 in A3

 

[Roger Govier]

Hi John

No need to enter -TODAY() in a cell, just use it in the formula. With your
next Birthday in cell A1, in B1 enter
=IF(A1>=TODAY(),A1-TODAY(),"Birthday has passed")

With regard to litres per 100 km converted to miles per gallon

1 gallon = 4.546 litres
1 mile = 1.6093 km or 1 km = 0.6214 miles 100 km = 62.14

=62.14/(A2/4.546)
or for your example of 7 litres/100km = 40.36 mpg


Regards

Roger Govier

 

[Bob Phillips]

Hi. Just wondered if someone brainy can help me with two formulas? The
first one I was wanting to enter todays date in a cell i.e. =today()

I also would like to have the date of my birthday next year 08/01/2006
in another cell.

I then want to take away todays date from that of my birthday and have
it formatted so it gives the number of days to go until my birthday.
So each different day I open the spreadsheet it will say one day less.
Maybe there is something that can be done with some sort of Count
query? Not being a wizz with formulas though I'm not sure the best way
to do this one.

A1: =TODAY()
A2: type 08/01/2006
A3: = A2-A1

The second formula I wish to do is a conversion of fuel consumption.
In the UK we calculate our fuel economy in MPG Miles per Gallon. In
Europe they like to do it a new metric way in litres needed for every
100 Km which nobody understands or can relate to.

A lot of car manufacturers are now just using the Euro way so it is a
pain to work out what the fuel consumption figures are for different
car models.

So if a car needs 7 litres of fuel for every 100 Km, I would like to
work out with a formula how many Miles per gallon consumption that
would equate to. Here in the UK it is 4.55 litres to every gallon. I'd
like to have it so I can enter the number in a cell and the next cell
it works it out in. I'm not precisely sure how many KM there are in a
Mile, but I'm sure someone in here might know?

=ROUND(CONVERT(kilometers*1000,"m","mi")/(CONVERT(litres,"l","gal")/1.2),0)

 

[John]

First setup the formatting of three cells:
1. select A1 and pull-down: Format > Cells.. > Number > Date
and then select a universal format like "14-mar-2001"

2. repeat above for cell A2

3. select A3 and format as General

Put =TODAY() in A1
Put your next birthday in A2
Put =A2-A1 in A3

Gary.

I tried this formula of =A2-A1, and I got a strange date value. It
gave me 20-Mar-00. I think maybe it is trying to give the value in
date format even though I selected it to be general as you said.

Do you know if there is a way to custom the formatting so it returns
the value in days?

I'd also love to know how I can select to have the date in the
following format for the first two cells dd/mm/yyyy. This is what I
always use here in the UK, but when I go into Format>Cells and Number,
and select date this way of doing the date is not one of the options
available for selection, and a lot of them have the month first.

Thanks

John

 

[John]

Hi John

No need to enter -TODAY() in a cell, just use it in the formula. With your
next Birthday in cell A1, in B1 enter
=IF(A1>=TODAY(),A1-TODAY(),"Birthday has passed")

Roger.

Yep. I think that one seems to do it. Thanks!

With regard to litres per 100 km converted to miles per gallon

1 gallon = 4.546 litres
1 mile = 1.6093 km or 1 km = 0.6214 miles 100 km = 62.14

=62.14/(A2/4.546)
or for your example of 7 litres/100km = 40.36 mpg

This is great too. I've formatted it to one decimal place and it's
looking pretty spot on. Cheers.

John