Need help formatting a cell.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can such a request be done using Excel?

I need to enter a time in Cell B18 of 2325 or 11:25pm
from that time i need it to calculate that cell
B17 is 15 minutes prior to B18 (2310 or 11:10pm)
B16 is 20 minutes prior to B18 (2305 or 11:05pm)
B15 is 45 minutes prior to B18 (2240 or 10:40pm)
B14 is 1 hour prior to B18 (2225 or 10:25pm)
B13 is 1 hour 10 minutes prior to B18 (2215 or 10:15pm)
and so one......

When i will need to change cell b18 to another time.... I need all my cells
to still have the same minutes prior to be calculated and deducted.

Sorry if questions is compliacted and confusing.
 
You can use a formula like this on B17
=IF($B$18>100,TIME(INT($B$18/100),MOD($B$18,100)-10,0),TIME(HOUR($B$18),MINUTE($B$18)-10,0))
And then change the -10 in the minute part on the different cells, or use
additional cells where you keep the differences and reference them instead of
hardcoding the numbers.

Hope this helps,
Miguel.
 
=B18-15/24/60
=B18-20/24/60
=B18-45/24/60
=B18-60/24/60
=B18-70/24/60

Format all cells like this

h:mm

Be careful you don't enter a time that isn't close to midnight (excel can't
handle negative time). You might need to enter the first time as date and
time like this

1/1/2000 12:30 AM

the format I gave you ignores the date and will show it as

0:30

if you format it like this

h:mm AM/PM
it will show as
12:30 AM
 
Back
Top